Results 1 to 12 of 12
  1. #1
    chrisdd is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    13

    VBA for SQL linked tables

    Hi.



    I'm using a VBA script that can be found here

    http://etutorials.org/Microsoft+Prod...es+at+Runtime/

    to link my access front end to an SQL database.

    I've recently added new tables and have added the names of them to the local table which stores the details of the tables to be linked - only they won't appear?

    I can link to them via the linked table manager in access no problem.

    Any help would be much appreciated.

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You need to provide some more info like screen shots of your table, etc. Have you tried to step through the code?

    Are you saying that if you start with a new Access database with NO linked tables and run the code some (the "older") appear and the new ones don't? Or do you run the code in an existing Access front-end that ALREADY has the older tables? If that is the case I suspect your code fails for every entry in you table, not only for the new ones. And you couldn't append new tabledef anyway if it already exists. The code you have should be modified to check if the table already exist and to refresh the connect string if it does rather than adding a new one (or delete it first).

    Anyway not much help can be offered without some specifics.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I kind of remember that if you add linked tables programmatically, you needed to do the checks as explained in the previous post, create the tables, append the tables to the collection and then call Application.RefreshDatabaseWindow to see them. But I must confess it's been a while.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe try to create a View of the table and see if you can link that in? Maybe not best solution but might work.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, you might look at the topic Is there VBA code to edit connections for linked tables?

    in the sectio Access of this forum

  7. #7
    chrisdd is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    13

    Code

    Quote Originally Posted by Gicu View Post
    You need to provide some more info like screen shots of your table, etc. Have you tried to step through the code?

    Are you saying that if you start with a new Access database with NO linked tables and run the code some (the "older") appear and the new ones don't? Or do you run the code in an existing Access front-end that ALREADY has the older tables? If that is the case I suspect your code fails for every entry in you table, not only for the new ones. And you couldn't append new tabledef anyway if it already exists. The code you have should be modified to check if the table already exist and to refresh the connect string if it does rather than adding a new one (or delete it first).

    Anyway not much help can be offered without some specifics.

    Cheers,

    Hi Code below,

    Public Sub cmdConnect_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim strServer As String
    Dim strDB As String
    Dim strTable As String
    Dim strConnect As String
    Dim strMsg As String

    On Error GoTo HandleErr


    strConnect = "ODBC;Driver={SQL Server};SERVER=server;DATABASE=database;UID=userde tails;PWD=*********;Trusted_Connection=No;APP=Micr osoft Office;"






    ' Get rid of any old links.
    'Call DeleteLinks

    ' Create a recordset to obtain server object names.
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
    If rst.EOF Then
    strMsg = "There are no tables listed in tblSQLTables."
    GoTo ExitHere
    End If

    ' Walk through the recordset and create the links.
    Do Until rst.EOF
    strServer = rst!SQLServer
    strDB = rst!SQLDatabase
    strTable = rst!SQLTable
    ' Create a new TableDef object.
    Set tdf = db.CreateTableDef(strTable)
    ' Set the Connect property to establish the link.
    tdf.Connect = strConnect & _
    "Server=" & strServer & _
    ";Database=" & strDB & ";"
    tdf.SourceTableName = strTable
    ' Append to the database's TableDefs collection.
    db.TableDefs.Append tdf
    rst.MoveNext
    Loop

    strMsg = "Connected to server successfully"

    rst.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing

    ExitHere:
    'MsgBox strMsg, , "Link SQL Tables"
    Exit Sub


    HandleErr:
    Select Case Err
    Case Else
    strMsg = Err & ": " & Err.Description
    Resume ExitHere
    End Select
    End Sub




    Public Sub DeleteLinks()
    ' Delete any leftover linked tables from a previous session.

    Dim tdf As DAO.TableDef

    On Error GoTo HandleErr
    For Each tdf In CurrentDb.TableDefs
    With tdf
    ' Delete only SQL Server tables.
    If (.Attributes And dbAttachedODBC) = dbAttachedODBC Then
    CurrentDb.Execute "DROP TABLE [" & tdf.Name & "]"
    End If
    End With
    Next tdf

    ExitHere:
    Set tdf = Nothing
    Exit Sub


    HandleErr:
    MsgBox Err & ": " & Err.Description, , "Error in DeleteLinks( )"
    Resume ExitHere
    Resume
    End Sub

  8. #8
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you please try this updated code, please note the comments I added (please post VBA code using the code tags - #-, makes it easier to follow and avoids the extra spaces inserted by the forum software):

    Code:
    Public Sub cmdConnect_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim strServer As String
    Dim strDB As String
    Dim strTable As String
    Dim strConnect As String
    Dim strMsg As String
    
    
    On Error GoTo HandleErr
    
    
    
    
    strConnect = "ODBC;Driver={SQL Server};SERVER=server;DATABASE=database;UID=userdetails;PWD=*********;Trusted_Connection=No;APP=Microsoft Office;"'Vlad: I assume you have the actual UID and PWD populated in this string as they don't seem to come from the tblSQLTables. I would also recommend using a newer SQL server driver - https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15
    
    
    
    
    ' Get rid of any old links.
    Call DeleteLinks  'Vlad - this was commented out so the add new was failing because you already had the table(s)
    
    
    ' Create a recordset to obtain server object names.
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
    If rst.EOF Then
    strMsg = "There are no tables listed in tblSQLTables."
    GoTo ExitHere
    End If
    
    
    ' Walk through the recordset and create the links.
    Do Until rst.EOF
    strServer = rst!SQLServer
    strDB = rst!SQLDatabase
    strTable = rst!SQLTable
    ' Create a new TableDef object.
    Set tdf = db.CreateTableDef(strTable)
    ' Set the Connect property to establish the link.
    
    
    'tdf.Connect = strConnect & "Server=" & strServer & ";Database=" & strDB & ";"
    tdf.Connect=Replace(Replace(StrConnect,"=server",strServer),"=database",strDB)
    tdf.SourceTableName = strTable
    ' Append to the database's TableDefs collection.
    db.TableDefs.Append tdf
    rst.MoveNext
    Loop
    db.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    strMsg = "Connected to server successfully"
    
    
    rst.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing
    
    
    ExitHere:
    'MsgBox strMsg, , "Link SQL Tables"
    Exit Sub
    
    
    
    
    HandleErr:
    Select Case Err
    Case Else
    strMsg = Err & ": " & Err.Description
    Resume ExitHere
    End Select
    End Sub
    
    
    
    
    
    
    
    
    Public Sub DeleteLinks()
    ' Delete any leftover linked tables from a previous session.
    
    
    Dim tdf As DAO.TableDef
    
    
    On Error GoTo HandleErr
    For Each tdf In CurrentDb.TableDefs
    With tdf
    ' Delete only SQL Server tables.
    'If (.Attributes And dbAttachedODBC) = dbAttachedODBC Then 
    if Instr(tdf.Name,"SQL Server")>0 Then 'Vlad: I use InStr to check if the connect string contains the words "SQL server", the previous line might work as well, but I never used it
    	CurrentDb.Execute "DROP TABLE [" & tdf.Name & "]"
    End If
    End With
    Next tdf
    
    
    ExitHere:
    Set tdf = Nothing
    Exit Sub
    
    
    
    
    HandleErr:
    MsgBox Err & ": " & Err.Description, , "Error in DeleteLinks( )"
    Resume ExitHere
    Resume
    End Sub
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  9. #9
    chrisdd is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    13

    Updated solution

    Hi Vlad,

    This is great thanks as the other code was causing an error when connecting to the database.

    I've added an extra table to the list but it still isn't showing though? The table I am trying to link to has a key field and this is identity property is set to on (I believe this sometimes causes an issue when linking to tables?)

    Any further advice very much appreaciated!



    Quote Originally Posted by Gicu View Post
    Can you please try this updated code, please note the comments I added (please post VBA code using the code tags - #-, makes it easier to follow and avoids the extra spaces inserted by the forum software):

    Code:
    Public Sub cmdConnect_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim strServer As String
    Dim strDB As String
    Dim strTable As String
    Dim strConnect As String
    Dim strMsg As String
    
    
    On Error GoTo HandleErr
    
    
    
    
    strConnect = "ODBC;Driver={SQL Server};SERVER=server;DATABASE=database;UID=userdetails;PWD=*********;Trusted_Connection=No;APP=Microsoft Office;"'Vlad: I assume you have the actual UID and PWD populated in this string as they don't seem to come from the tblSQLTables. I would also recommend using a newer SQL server driver - https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15
    
    
    
    
    ' Get rid of any old links.
    Call DeleteLinks  'Vlad - this was commented out so the add new was failing because you already had the table(s)
    
    
    ' Create a recordset to obtain server object names.
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
    If rst.EOF Then
    strMsg = "There are no tables listed in tblSQLTables."
    GoTo ExitHere
    End If
    
    
    ' Walk through the recordset and create the links.
    Do Until rst.EOF
    strServer = rst!SQLServer
    strDB = rst!SQLDatabase
    strTable = rst!SQLTable
    ' Create a new TableDef object.
    Set tdf = db.CreateTableDef(strTable)
    ' Set the Connect property to establish the link.
    
    
    'tdf.Connect = strConnect & "Server=" & strServer & ";Database=" & strDB & ";"
    tdf.Connect=Replace(Replace(StrConnect,"=server",strServer),"=database",strDB)
    tdf.SourceTableName = strTable
    ' Append to the database's TableDefs collection.
    db.TableDefs.Append tdf
    rst.MoveNext
    Loop
    db.TableDefs.Refresh
    Application.RefreshDatabaseWindow
    strMsg = "Connected to server successfully"
    
    
    rst.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing
    
    
    ExitHere:
    'MsgBox strMsg, , "Link SQL Tables"
    Exit Sub
    
    
    
    
    HandleErr:
    Select Case Err
    Case Else
    strMsg = Err & ": " & Err.Description
    Resume ExitHere
    End Select
    End Sub
    
    
    
    
    
    
    
    
    
    
    Public Sub DeleteLinks()
    ' Delete any leftover linked tables from a previous session.
    
    
    Dim tdf As DAO.TableDef
    
    
    On Error GoTo HandleErr
    For Each tdf In CurrentDb.TableDefs
    With tdf
    ' Delete only SQL Server tables.
    'If (.Attributes And dbAttachedODBC) = dbAttachedODBC Then 
    if Instr(tdf.Name,"SQL Server")>0 Then 'Vlad: I use InStr to check if the connect string contains the words "SQL server", the previous line might work as well, but I never used it
        CurrentDb.Execute "DROP TABLE [" & tdf.Name & "]"
    End If
    End With
    Next tdf
    
    
    ExitHere:
    Set tdf = Nothing
    Exit Sub
    
    
    
    
    HandleErr:
    MsgBox Err & ": " & Err.Description, , "Error in DeleteLinks( )"
    Resume ExitHere
    Resume
    End Sub
    Cheers,

  10. #10
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Chris,
    Have you stepped through the code? Are all the old links deleted then recreated and you are missing this new table? The fact that the primary key has the identity property on (acting like an autonumber) would not prevent it from being linked I think, in the worse case it will be read-only if you don't specify\set the unique identifier field, which can also be done in VBA (even if it is a linked table):
    Code:
    CurrentDb.Execute "CREATE INDEX PrimaryKeyIdx ON TableName (YourPKField) WITH PRIMARY"
    https://stackoverflow.com/questions/...th-primary-key

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    chrisdd is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    13

    isam issue

    Quote Originally Posted by chrisdd View Post
    Hi Vlad,

    This is great thanks as the other code was causing an error when connecting to the database.

    I've added an extra table to the list but it still isn't showing though? The table I am trying to link to has a key field and this is identity property is set to on (I believe this sometimes causes an issue when linking to tables?)

    Any further advice very much appreaciated!


    Hi,

    Thanks for the help - I've just switched the error handler back on and it appears I have a 3170 Could not find installable ISAM error?

    Apparently this suggests something wrong with the connection string? Could this be a server configuration issue where some changes have been made?

    Thanks in advance

    Chris

  12. #12
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Chris,
    I would recommend that you download and install the latest ODBC driver for SQL server (I included the link in the updated code I posted). Then you need to update the strConnect variable to make it use the new driver.
    If you go to the External Data tab on the ribbon and choose ODBC what SQL server driver do you see?
    Cheers,

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

Similar Threads

  1. Replies: 6
    Last Post: 04-18-2018, 06:56 AM
  2. Replies: 17
    Last Post: 04-09-2018, 04:39 PM
  3. Replies: 1
    Last Post: 02-07-2016, 02:41 PM
  4. Replies: 4
    Last Post: 11-22-2013, 11:20 AM
  5. Replies: 5
    Last Post: 02-02-2012, 06:42 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