Solved VBA Database.Execute("CreateTable...") - Table created in CurrentDB, not the specified DB

[jhg]

I have a front-end database that links tables from a couple of back-end databases (all MSACCESS/DAO). As part of a schema upgrade I executed the following:

    Dim dbLog As Database
    Set dbLog = ws.OpenDatabase(CurrentProject.path & "\" & "Log.accdb")
    ...
    dbLog.execute "create table ...."    ' A valid CREATE TABLE statement

Intending to follow up by creating a link to the table from the front-end to the backend.

This executed successfully, but the table was created in CurrentDB (the front-end) not the specified backend.

Is this expected? What am I missing?

 

[CJ_London]

you seem to be missing some code - ws is not declared, what is the code represented by ... and personally I would use dao.database rather than just database

 

[theDBguy]

Would using OpenCurrentDatabase work?

Just thinking out loud...

 

[arnelgp]

sample code:

    ' valid create table sql
    sql = "CREATE TABLE Employees (" & _
             "EmployeeID AUTOINCREMENT PRIMARY KEY, " & _
             "FirstName TEXT(50), " & _
             "LastName TEXT(50), " & _
             "HireDate DATETIME, " & _
             "Salary CURRENCY)"
    Dim dbLog As Database
    Set dbLog = DBEngine.OpenDatabase(CurrentProject.Path & "\Log.accdb")
    dbLog.Execute sql
    dbLog.Close

 

[Ken Sheridan]

The following code snippet creates a temporary external database, and then creates two tables in it. Finally it creates links in the current database to the two tables:

    Dim dbs As DAO.Database, dbsTemp As DAO.Database, qdf As DAO.QueryDef, tdf As DAO.TableDef
    Dim varFld As Variant
    Dim n As Integer
    
    Set dbs = CurrentDb
    
    ' create temporary database in same folder as current database
    ' or return reference to database if already exists
    strTempDb = Left$(dbs.Name, Len(dbs.Name) - Len(Dir(dbs.Name))) & "BoMTemp.accdb"
    On Error Resume Next
    Set dbsTemp = CreateDatabase(strTempDb, dbLangGeneral)
    If Err <> 0 Then
        Set dbsTemp = OpenDatabase(strTempDb)
    End If
    On Error GoTo Err_Handler
    
    ' create BoM table in temporary database if doesn't exist
    strSQL = "CREATE TABLE BoM (MajorPartNum LONG NOT NULL, " & _
            "MinorPartNum LONG NOT NULL, Quantity LONG NOT NULL, " & _
            "CONSTRAINT MajorMinor PRIMARY KEY (MajorPartNum, MinorPartNum) )"
    On Error Resume Next
    dbsTemp.Execute (strSQL)
    On Error GoTo Err_Handler
    
    ' create BoM_Temp table in temporary database.
    ' note that this one has no constraint as it will be
    ' necessary to add duplicates of major/minor part number
    ' values in separate rows
    strSQL = "CREATE TABLE BoM_Temp (MajorPartNum LONG NOT NULL, " & _
            "MinorPartNum LONG NOT NULL, Quantity LONG NOT NULL)"
    dbsTemp.Execute (strSQL)
    
    ' check if link to BoM_Temp table exists and if not create link
    On Error Resume Next
    Set tdf = dbs.TableDefs("BoM_Temp")
    If Err <> 0 Then
        DoCmd.TransferDatabase acLink, "Microsoft Access", strTempDb, acTable, "BoM_Temp", "BoM_Temp"
    Else
        varFld = tdf.Fields(0)
        If Err <> 0 Then
            ' refresh link if current link invalid
            tdf.Connect = ";DATABASE=" & strTempDb
            tdf.RefreshLink
        End If
    End If
    Err.Clear
        
    ' check if link to BoM table exists and if not create link
    Set tdf = dbs.TableDefs("BoM")
    If Err <> 0 Then
        DoCmd.TransferDatabase acLink, "Microsoft Access", strTempDb, acTable, "BoM", "BoM"
    Else
        varFld = tdf.Fields(0)
        If Err <> 0 Then
            ' refresh link if current link invalid
            tdf.Connect = ";DATABASE=" & strTempDb
            tdf.RefreshLink
        End If
    End If
    Err.Clear

 

[jhg]

you seem to be missing some code - ws is not declared, what is the code represented by ... and personally I would use dao.database rather than just database

Well, in the famous words of Miss Emily Litella, "never mind".

I figured out my problem, and it was a result of revisiting very old code and forgetting what I intended when I wrote it.

Sorry for the meaningless noise.

 

[jdraw]

jhg,
Since you marked this solved, would you share your code so others could see the solution.
Thanks.

 

[Gasman]

jhg,
Since you marked this solved, would you share your code so others could see the solution.
Thanks.

Don't you just hate it when this happens.