CreateDatabase & OpenDatabase DAO problem...-VBForums
Results 1 to 6 of 6

Thread: CreateDatabase & OpenDatabase DAO problem...

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2001
    Location
    Scotland, UK
    Posts
    68

    Question CreateDatabase & OpenDatabase DAO problem...

    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

  2. #2
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,482
    Code:
    ...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 :
    Code:
    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.

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jul 2001
    Location
    Scotland, UK
    Posts
    68
    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?

  4. #4
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,482
    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.

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jul 2001
    Location
    Scotland, UK
    Posts
    68
    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.

  6. #6
    Evil Genius alex_read's Avatar
    Join Date
    May 2000
    Location
    Espoo, Finland
    Posts
    5,482
    Set db = ws.OpenDatabase(Path2Db, False, False, ";PWD=test")
    Will be all you need for this ( http://forums.vb-world.net/showthrea...threadid=63719 )
    Last edited by alex_read; Nov 8th, 2001 at 02:31 AM.

    Please rate this post if it was useful for you!
    Please try to search before creating a new post,
    Please format code using [ code ][ /code ], and
    Post sample code, error details & problem details

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.