Access - conversion from mdb to accdb and Error "#type!"

Question

I converted my Access database from the mdb-format to accdb.

A Private Function to collect all the mail-adresses in my database in order to put them in a mail does not work anymore in accdb. Executing the function gives me the Error code "#type!" but without any hint what's wrong with ist

This is the code of the Private Function:

Private Function emailTeilnehmer()

Dim db As Database, r As Recordset, it As Variant, sql As String

Set db = CurrentDb()

sql = "SELECT * FROM CIS_TN_email"

Set r = db.OpenRecordset(sql)

it = ""

If Not r.EOF Then

r.MoveFirst 

Do While Not r.EOF 

    it = it + r!email + "," 

    r.MoveNext 

Loop 

End If 

emailTeilnehmer = it 

End Function

What do I have to change that it is working again?

Thanks for helping me.

Thomas

Answer 1

In addition to what Hans said:

> it = it + r!email + ","

This is incorrect. In VBA the string concatenation operator is "&"

Getting away with it depends on language features that may change over time. Rewrite this as:

it = it & r!email & ";"

(note how I am separating email addresses with semicolon as is the standard)

Answer 2

Does it help if you change

Dim db As Database, r As Recordset, it As Variant, sql As String

to

Dim db As DAO.Database, r As DAO.Recordset, it As String, sql As String

and

Set r = db.OpenRecordset(sql)

to

Set r = db.OpenRecordset(sql, dbOpenDynaset)