CreateDatabase & OpenDatabase DAO problem...

[XanderSolo]

Can anyone help me with this problem I'm having with a VB app I'm building?

I use some code which creates an access .mdb file and then calls another sub to poulate a table in the .mdb file with data. The pagenames table in the DB has two fields pageid (autonumber) & pagename (text), I want to populate the pagename field with data.

When I run this code the DB, table & fields are created as they should be but the code errors out at the LoadPageData sub & I don't know why.....

Any help would be much appreciated............

Private Sub BuildDb()
Dim ws As Workspace
Dim db As Database
Dim tbldef As TableDef
Dim flddef As Field
Dim siteDb As String

Path2Db = App.Path & "\projects\" & projname & "\mydb.mdb"
Set ws = DBEngine.Workspaces(0)
Set db = ws.CreateDatabase(siteDb, dbLangGeneral & ";pwd=" & pass, dbEncrypt)

Set tbldef = db.CreateTableDef("pagenames")
Set flddef = tbldef.CreateField("pageid", dbLong, 0)
flddef.Attributes = dbAutoIncrField
tbldef.Fields.Append flddef
Set flddef = tbldef.CreateField("pagename", dbText, 13)
tbldef.Fields.Append flddef
db.TableDefs.Append tbldef
db.Close
LoadPageData Path2Db
End Sub

Private Sub LoadPageData(Path2Db)
Dim ws As Workspace
Dim db As Database
Dim myRS As Recordset
Dim fld As Field

Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase(Path2Db, False, False, "PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & siteDb & ";PWD=test")
Set myRS = db.OpenRecordset("pagenames", dbOpenTable)
If myRS.BOF And myRS.EOF Then
myRS.AddNew
myRS.Fields("pagename") = "default"
myRS.Update
myRS.MoveLast
Else
End If
End Sub

[alex_read]

...db.Close 
LoadPageData Path2Db...

Try killing all of the objects correctly first (take a look at OBJECTS and the SET, and the NOTHING keywords in your msdn helpfiles).

instead of the above, use :

set flddef = nothing
set tbldef = nothing
db.close
set db = nothing
set ws = nothing

Note, I'm not sure apart from the db which objects you have to clost before setting them to nothing. Also, please put the code tags [ code ] and [ /code ] around your coding.

Lastly, you won't need the movelast part in there.

[XanderSolo]

Thanks for the pointers yesterday, I tried them but the app. failed on this line

Set db = ws.OpenDatabase(Path2Db, False, False, "PROVIDER=Microsoft.Jet.OLEDB.4.0; DATA SOURCE=" & siteDb & ";PWD=test")

The error message I got was

Run-time error '3151'
ODBC -- connection to 'path 2 my access Db' failed


I have no idea where I'm going wrong. Can you help?

[alex_read]

Run this again, and when this line is halted, bring up the immediate window (from the view menu).

Type in ?Path2Db and hit enter.
Then type ?siteDb and hit enter.

Please post back the value of these variables (what comes up on the screen) when the program is halted.

[XanderSolo]

I made a mistake when typing in the code segment. There is only one variable used called Path2Db, siteDb should be called Path2Db also.

Path2Db variable contains the string

"C:\test\myDb.mdb" which is the path to the access database created by my code.

[alex_read]

Set db = ws.OpenDatabase(Path2Db, False, False, ";PWD=test")
Will be all you need for this ( http://forums.vb-world.net/showthrea...threadid=63719 )