OpenDatabase VBA

[Ciaran]

I need to run some VBA behind a command button which will run some insert sql. However, I dont know how to open the database. What code do I need? I have the following so far:

Private Sub cmd_Execute_Click()

Dim dbs As DAO.Database
Dim Database As DAO.Database

Set Database = CurrentDb
Set dbs = OpenDatabase(Database)

dbl.Execute " INSERT INTO tbl_M:M_Form/State " _
& "(Form_Number,State_ID) VALUES " _
& "('TestForm, 'AK',);"

dbs.Close

End Sub

I'm getting a user defined type not defined for the first line. My VB knowledge has deserted me. What do I need to fix this? Define a Database type variable, but how do I do that?

Any help would be great

Ciaran

 

[Mile-O]

You are using DAO when your Access is set up to use ADO.

DAO (Data Access Object) was Microsoft's method of data access back in Access '97 but has been replaced since with ADO (ActiveX Data Object.)

If you wish to restore DAO to your version of Access then:

i) open a module
ii) goto Tools -> References
iii) Find Microsoft Data Access Objects 3.6 and check it
iv) Move the aforementioned library's priority above that of Microsoft ActiveX Data Objects


Your code should run now...

 

[Ciaran]

Well, I'm not using DAO or ADO. All this is sounding familiar, but its been a while.

Thats all the code I have, so should I just change it to ADO and activate the reference?

Ciaran

 

[dcx693]

Dim dbs As DAO.Database

Well, I'm not using DAO or ADO.

Hmm....sounds like you're trying to use DAO to me.

 

[Ciaran]

I have most of it working now with DAO. I'm getting a sql syntax error with :

dbs.Execute "INSERT INTO tbl_M:M_Form/State (Form_Number,State_ID) VALUES ('Test','Test_State');"

Ciaran

 

[Ciaran]

I figured it out, it was the characters in my table name that were screwing things up. I renamed the table and now it works.

 

[Mile-O]

tbl_M:M_Form/State

That's probably the best example of why you shouldn't put symbols into your table names, control names, etc.

Put square brackets around that table name:

[tbl_M:M_Form/State]