Why this code can not compile in my database?

[longmatch]

I just got this code from "Access VBA 2000 handbook" book. It can compile when I ran it using their database file. But when I copied this code to a new database, It can not compile. I have checked the references, making sure I have Microsoft DAO 3.6 object and Microsoft Jet and Replicatioin object. I do not know why.

Haijun

Public Sub NewTable()
Dim db As Database, tdf As TableDef
Dim fld1 As Field, fld2 As Field, fld3 As Field, fld4 As Field
Set db = CurrentDb
Set tdf = db.CreateTableDef("tblEmployeeExpenses&quot
Set fld1 = tdf.CreateField("ExpenseID", dbLong)
fld1.Required = True
' To increment the value for new records
fld1.Attributes = dbAutoIncrField
Set fld2 = tdf.CreateField("EmployeeID", dbLong)
fld2.Required = True
Set fld3 = tdf.CreateField
With fld3
.Name = "ExpenseType"
.Required = True
.Type = dbText
.Size = 30
End With
Set fld4 = tdf.CreateField("Amount", dbCurrency)
With tdf.Fields
.Append fld1
.Append fld2
.Append fld3
.Append fld4
End With
db.TableDefs.Append tdf
RefreshDatabaseWindow
End Sub

 

[KyleS]

Haijun,
Which version of Access are you using? And when it won't compact what is the error is gives you? Which line does it stop on?

Kyle

 

[longmatch]

Hi Kyle:
I am using Microsoft office 2000 - Access. When I ran this code in the immediate window, it gave me the error message "Method or data member not found" for fld.required.

If you need any information, let me know. You can copy this code into your Access module, and run it to see whether you get the same message.

Thank you

haijun

 

[KyleS]

Haijun,
Copying it into my own DB won't help much since I'm using Access '97, but I think the problem may still be your references... Access 2000 defaults to using ADO and since you're using DAO you need to specify in your declare statments.

Dim db As DAO.Database, tdf As DAO.TableDef

Give this a try and see...

Kyle :

 

[longmatch]

I tried your suggestion, it did not work. I also recheck the reference window. It is the same as the setting of the original database.

Haijun

 

[MichaelRed]

The syntax shown is more along the lines of Ms. A ver '97 or earlier. If you are using 2K, look into the help for dreatefield and related topics. Much of what is in the post is unnecessarily complicated, and some of it is actually impropper for 2K.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[AccessMoron]

Did you copy the procedure RefreshDatabaseWindow into your new DB as well?

 

[AccessMoron]

OK looking into it a bit more deeply, it would appear that Access thinks that fld is an ADO variable.
Change the DIM to
Dim fld1 As DAO.Field, fld2 As DAO.Field, fld3 As DAO.Field, fld4 As DAO.Field

 

[930driver]

I just tested this in A2K. It doesn't run nor compile until I have set a reference to the DAO object library, and prefixed Database, Tabledef and Field with DAO. Then it runs fine.

Alternatively to prefixing with DAO you can set the reference to the DAO library higher in the list of references so that it is checked first.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[KyleS]

Sorry Haijun,

I didn't explicitly tell you to make the fields ADo as well, that would have saved everbody here some time (sorry...)

 

[longmatch]

Thanks for everyone who gave me help. This problem is solved by changine the priority of references. It looks like that Microsoft software is not perfect.


Haijun

 

[930driver]

This behavior is by design. It looks for classes and functions in each library, starting with the first until it finds one, then it quits. If it searched through all of them and found the same one (as in your case) it wouldn't know which one to use.

So you either prioritise your references, or you prepend your call with the library you specifically want it to use, in your case DAO.
"The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"