Results 1 to 8 of 8

Create Table on "BackEnd DB"

  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,299

    Create Table on "BackEnd DB"

    The following code executes successfully when dbs Set to open Currentdb but if I Set dbs to open backend, as you see below I'm met with the error you see pictured in the accompanying screenshot.



    The current Frontend where the code is being run already has 6 tables linked to backend "\\Kitchen\gcc\gcc data.mdb", so what might be the problem?

    Code:
    Option Compare Database
    Option Explicit
    
    Sub tblNew()
    Dim dbs As DAO.Database
    
    Set dbs = OpenDatabase("\\Kitchen\gcc\gcc data.mdb'")
    
    dbs.Execute "CREATE TABLE tblNewTable (TWCmdID AUTOINCREMENT CONSTRAINT TWCmdID PRIMARY KEY)"
    
    Set dbs = Nothing
    
    End Sub
    Click image for larger version. 

Name:	000.jpg 
Views:	14 
Size:	31.2 KB 
ID:	39028

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,135
    For chuckles, try the code from a file not linked to the back end.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,299
    5 seconds before you chimed in I had just rebooted my machine and, as soon as I removed the erroneous quote after "mdb", the code ran fine and created the new table on the backend. The only explanation I can think of is our router got rebooted last night whereas neither of the computers did as well. Maybe that sent Win 10 communications into a tizzy............ who knows!

    So now the task becomes how to link to the new table. Can that be done with some sort of connect statement?

  4. #4
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,082
    So now the task becomes how to link to the new table. Can that be done with some sort of connect statement?
    how about docmd.transferdatabase?

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,135
    Ah, I didn't notice that quote. Glad you got it sorted.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,299
    The transferdatabase I coded was attempting to link the table "tblNewTableIV" just created in dbs to the frontend dbsfe retaining the same name. My first time using the method so my newly acquired understanding of the method is seemingly wrong What am I missing here?

    Code:
    Option Compare Database
    Option Explicit
    
    Sub tblNew()
    Dim dbs As DAO.Database
    Dim dbsFE As DAO.Database
    
    Dim BackendPath As String
    
    BackendPath = Mid(CurrentDb.TableDefs("InstProperties").Connect, 11)  'Skip over the ;DATABASE=
    Set dbs = OpenDatabase(BackendPath)
    Set dbsFE = CurrentDb()
    
    dbs.Execute "CREATE TABLE tblNewTableIV (TWCmdID AUTOINCREMENT CONSTRAINT TWCmdID PRIMARY KEY)"
    
    DoCmd.TransferDatabase acLink, dbs, dbsFE, acTable, "tblNewTableIV", "tblNewTableIV"
    
    Set dbs = Nothing
    
    End Sub
    Click image for larger version. 

Name:	001.jpg 
Views:	11 
Size:	19.8 KB 
ID:	39031

  7. #7
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    284
    Code:
    DoCmd.TransferDatabase acLink, dbs, dbsFE, acTable, "tblNewTableIV", "tblNewTableIV"
    Your second argument (dbs) is wrong. It should be the database type, which should be "Microsoft Access" (including the quotes as it is a string argument)

    More here ... https://docs.microsoft.com/en-us/off...ansferdatabase

  8. #8
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,299
    Code:
    DoCmd.TransferDatabase acLink, "Microsoft Access", BackendPath, acTable, "tblNewTableII", "tblNewTableII"
    The third argument (dbsFE) was also wrong. A string expression of the fully qualified backend DB is the expected argument.

    Thanks,
    Bill

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 1
    Last Post: 07-23-2015, 10:18 PM
  4. Replies: 2
    Last Post: 08-05-2013, 06:36 PM
  5. Replies: 1
    Last Post: 05-13-2010, 10:50 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums