Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Need a temporary table link to different backend

    Trying to debug a little test code. I need to temporarily link to a table in a different db other than the currentdb. I get a Type Mismatch on the TableDef name assignment so there's obviously something I don't know about TableDef objects.



    Code:
    Option Compare Database
    Option Explicit
    Const TMSHub As String = "c:\eRep\eRepData.mdb"
    Private Sub testlink()
    
    LinkTMS ("AC530")
    End Sub
    Private Function LinkTMS(tblName As String)
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    '  Link to tblName on the TMS Hub
    '*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
    Dim tdf As DAO.TableDef
    
    Set tdf = tblName
       
    tdf.Connect = "; DATABASE=" & TMSHub                    'Yes, specify new BE
    
    Set tdf = Nothing
    
    End Function



    I also tried to include the table name in the tdf.Connect, but that doesn't work either.
    Code:
    'Set tdf = tblName
       
    tdf.Connect(tblName) = "; DATABASE=" & TMSHub

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Steve Bishop has a video (28 min) on connecting Access FE to SQL Server BE using VBA. Same principle would apply to connecting to an Access BE. https://www.youtube.com/watch?v=soSKpnM4wcg

    Also see Access 2013 connection strings


    Maybe here also??? How to Make a Connection in DAO
    Last edited by ssanfu; 06-28-2019 at 06:20 PM. Reason: added links

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Sorry, Bishop goes to fast for this old geezer. The answer to my issue is probably in there somewhere but I didn't catch it.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This works for me:

    Set tdf = db.TableDefs(tblName)

    I can't remember if the db variable is required here. In many things it isn't but in a few it is.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    It needed the db but fails anyway.
    Click image for larger version. 

Name:	000.jpg 
Views:	23 
Size:	142.8 KB 
ID:	38938

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You declared db but didn't set it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Does the db need to be set to the TMSHub? As you can see from the screenshot the Currentdb didn't get the job done.

    Should it be "Set db = TMSHub"? I don't think I've ever seen such an assignment.

    Click image for larger version. 

Name:	000.jpg 
Views:	23 
Size:	148.1 KB 
ID:	38939

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    depends what you want to do with the table once you have opened it, you can just do this in sql without requiring tabledef


    SELECT * FROM tblName IN 'C:\pathtoDB\DBName.accdb'

    so either put the sql in a query or assign it to a recordset.

  9. #9
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Take a look at my temp database example. In the CreateTable procedure there are a couple methods of connecting.
    Hope it helps.

    ExampleTempDatabase.zip

  10. #10
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I realize I can SELECT into a DAO Recordset, but I was hoping "this" would work also............. NOT!
    Click image for larger version. 

Name:	001.jpg 
Views:	20 
Size:	91.6 KB 
ID:	38945

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Unless something has changed, the table/query argument of a domain aggregate function has to be a saved object, not SQL.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    It seems you are right, nothing has changed. Is there parameters to dbengine(0)(0) such that one can stipulate that ReadOnly is being requested? Coding as I did in Test2 is met with failure because the resource is open by another user, which it isn't BTW.

    Code:
    Sub test2()
    Const strTMSHost As String = "\\192.168.1.8\GCC\GCC DATA.mdb"
    Dim rs As DAO.Recordset
    Dim FromPhone As String
    Dim strSQL
    
    strSQL = "SELECT * FROM InstProperties IN '" & strTMSHost & "'"
    Set rs = DBEngine(0)(0).OpenRecordset(strSQL)
    
    MsgBox rs!InstTWNumber
    
    Set rs = Nothing
    
    End Sub

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    DLookup did not have filter criteria. What record do you want to pull value from?

    This worked for me:
    Code:
    Sub test()
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Rates IN 'C:\Users\June\Umpires.accdb'")
    Debug.Print rs!RateID
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I can see where that would be a mystery to you. Table InstProperties is a single record table where the primary app's settings are stored.

  15. #15
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    having an IP address in your path should in itself not matter to access. Personally it is a technique I use all the time and have never had a problem connecting - even to excel - which may already be open by another users

    however I don't use dbengine, just currentdb or set an object to currentdb and use that - MS do not recommend using dbengine

    Code:
    Sub test2()
    Const strTMSHost As String = "\\192.168.1.8\GCC\GCC DATA.mdb"
    Dim rs As DAO.Recordset
    Dim FromPhone As String
    Dim strSQL
    
    strSQL = "SELECT * FROM InstProperties IN '" & strTMSHost & "'"
    Set rs = currentdb.OpenRecordset(strSQL)
    
    MsgBox rs!InstTWNumber
    
    Set rs = Nothing
    
    End Sub
    Is there parameters to dbengine(0)(0) such that one can stipulate that ReadOnly is being requested?
    openrecordset has parameters - see this link https://docs.microsoft.com/en-us/off...set-method-dao

    one of them is snapshot - which you can set in a query anyway

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-19-2019, 03:03 PM
  2. Replies: 21
    Last Post: 07-06-2015, 01:51 PM
  3. Replies: 8
    Last Post: 01-27-2014, 12:41 PM
  4. break the link to a backend table
    By markjkubicki in forum Programming
    Replies: 1
    Last Post: 06-28-2013, 10:49 PM
  5. Replies: 5
    Last Post: 11-13-2012, 12:16 PM

Posting Permissions

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