Grab a table from an SQL server

Why Linked???

you can connect via ADODB and use the data as needed with out it being linked.

Dim tCN as adodb.Connection
 
Public Sub ADOConnect()
    Set tCN = New ADODB.Connection
    Dim ConString As String, Server As String
    
    
    ConString = "Driver={SQL Server};" & "Server=MySQLServer;Database=MyData;Uid=MyUser;Pwd=myPswd;"
    On Error GoTo DatabaseConnectionError
    With tCN
        .ConnectionString = ConString
        .CursorLocation = adUseClient
        .CommandTimeout = 60
        .Open
    End With
    Set ADOConnect = tCN
    Exit Function
DatabaseConnectionError:
    Err.Raise errDatabaseConnection, "ADOConnect", "Unable to Connect to DB"
    Set ADOConnect = Nothing
End Function

Select allOpen in new window

The VBA code below is used to link an Access front end to a SQL table.
The frmSQLConnectionsHidden uses a table with one row for each SQL table to be opened. If you are linking to only one SQL table set strGetTableName ="NameOfSQLTable"
The qrySQLLinkedTablesHowMany counts the number of rows in the table that has one row for each SQL table to be opened. If you are opening only one table change While intCountTables <= DLookup("[HowMany]", "qrySQLLinkedTablesHowMany") to While intCountTables <= 1

Hope this is helpful.
Tim Sledge

Option Compare Database
Option Explicit
Public strServerName As String
Public strDatabaseName As String
Public strUserName As String
Public strPWD As String
 
 
 
Public Sub subLinkSQLServerTablesAndViews()
 
Dim strGetTableName As String
Dim strGetDatabaseName As String
Dim intCountTables As Integer
 
On Error GoTo Exit_subLinkSQLServerTablesAndViews
 
'get SQL information from the tblSQLLocationInformation and tblSQLLocationCurrent
strServerName = DLookup("[ServerName]", "qrySQLConnectionInfo")
strGetDatabaseName = DLookup("[DatabaseName]", "qrySQLConnectionInfo")
strUserName = DLookup("[UserName]", "qrySQLConnectionInfo")
strPWD = DLookup("[Password]", "qrySQLConnectionInfo")
 
 
intCountTables = 1
 
DoCmd.OpenForm "frmSQLConnectionsHidden", acNormal, , , acFormEdit, acHidden
DoCmd.GoToRecord , "frmSQLConnectionsHidden", acFirst
 
While intCountTables <= DLookup("[HowMany]", "qrySQLLinkedTablesHowMany")
    
    strGetTableName = Forms!frmSQLConnectionsHidden!SQLLinkedTablesName
    'function below attempts to link the SQL table or view and returns 1 if there were no errors in linking the table
    If LinkTableDAO(strServerName, strGetDatabaseName, strGetTableName, strGetTableName, strUserName, strPWD) = -1 Then
       ' MsgBox "just linked " & strGetTableName
       ' MsgBox LinkTableDAO(strServerName, strGetTableName, "dbo_" & strGetTableName, strUserName, strPWD)
        GoTo OK
    Else
        GoTo NoLoadup 'stop the linking process - an error occured
    End If
 
OK: 'keep going - link was successful
 
'go to the next record in the list of SQL tables if there are any records left
If intCountTables < DLookup("[HowMany]", "qrySQLLinkedTablesHowMany") Then
    DoCmd.GoToRecord , "frmSQLConnectionsHidden", acNext
End If
 
intCountTables = intCountTables + 1
 
Wend
 
DoCmd.Close acForm, "frmSQLConnectionsHidden", acSaveNo
 
Dim fFormOpen As Integer
fFormOpen = IsFormOpen("frmSQLConnections")
  If fFormOpen Then
    Beep
    MsgBox "Tables connected successfully."
  End If
 
 
Exit_subLinkSQLServerTablesAndViews:
    Exit Sub
 
NoLoadup:
 
MsgBox "Could not load " & strGetTableName & " The program will shut down."
 
'DoCmd.Quit
 
Err_subLinkSQLServerTablesAndViews:
    MsgBox Err.Description & " " & Err.Number
    Resume Exit_subLinkSQLServerTablesAndViews
    
End Sub
Public Function LinkTableDAO(strServerName As String, strDatabaseName As String, strTableName As String, strLinkName As String, strUserName As String, strPWD As String) As Boolean
 
'requires Microsoft DAO 3.6 Object Reference Library to be loaded under Tools/References
 
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim intTrustedConnection As Integer
 
If IsNull(DLookup("[Trusted]", "qrySQLLocationCurrent")) = False Then
    intTrustedConnection = DLookup("[Trusted]", "qrySQLLocationCurrent")
Else
    intTrustedConnection = 0
End If
    
On Error Resume Next
Set db = CurrentDb
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
    db.TableDefs.Delete strLinkName
    db.TableDefs.Refresh
Else
    Err.Clear 'clear any previous errors
End If
 
Set tdf = db.CreateTableDef(strLinkName) 'create a linked table that will be named as defined by strLinkName
 
If intTrustedConnection = 1 Then 'connect to SQL as trusted connection
    tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabaseName & ";Trusted_Connection=Yes"
Else  'connect to SQL using SQL username and password
    tdf.Connect = "ODBC;Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabaseName & ";UID=" & strUserName & ";PWD=" & strPWD
End If
 
 
'tdf.Connect = ";database=c:\mydb.mdb"
 
tdf.SourceTableName = strTableName 'what is the name of the table in SQL to be linked
db.TableDefs.Append tdf  'append the table defined and named by "Set tdf = " to the Access database
LinkTableDAO = (Err = 0) 'true if No Errors False if Error
 
End Function

Select allOpen in new window

This project started out having an MS Access back-end.  Now it has an SQL back-end.  I need to have the linked tables operate in the background so the user is oblivious to their existence.

Ive added DAO objects 2.8 to my project and pasted your code into it.  Ive added more code to create an ADOBD.Recordset.
Is there a way to make a permanent link to the SQL Table using said recordset?
Thanks!

Public Function ADOConnect()
    Dim tCN As ADODB.Connection
    Set tCN = New ADODB.Connection
    Dim ConString As String, Server As String
    
    
    ConString = "Driver={SQL Server};" & "Server=DaServer\dev1" & _
                ";Database=" & "daDB" & ";Uid=" & "da_user" & _
                ";Pwd=" & "da_PW;"
    On Error GoTo DatabaseConnectionError
    With tCN
        .ConnectionString = ConString
        .CursorLocation = adUseClient
        .CommandTimeout = 60
        .Open
    End With
    Dim db As Database
    Set db = CurrentDb
    Dim rs As New ADODB.Recordset
    Set rs = tCN.Execute("SELECT * FROM da_table")
    rs.MoveLast: rs.MoveFirst
    Debug.Print rs.RecordCount
    Set ADOConnect = tCN
 
    
    Exit Function
DatabaseConnectionError:
    'NOTE: The line below caused an error at compile time.
    'Err.Raise errDatabaseConnection, "ADOConnect", "Unable to Connect to DB"
    Set ADOConnect = Nothing
End Function

Select allOpen in new window

The code I posted creates a link which is what you requested.  This link should stay in place even when you open the database again after closing it.  If the link does not re-appear, you can make the code run each time the application is opened.

RDWaibel's code is actually the best way to do it, because it only connects to do the task needed.  However, if you want the table to stay linked like the access tables, use the code I posted.

Tim's method is the best way attach the table.

TimSledge:
I believe need to stay connected because I have forms that are based on the tables in question.
I ran the code you posted.  It ran fine, but I still have the same problem as before.  When I start the Access project I get an error:
          Connection failed:
          SQL Server error: 18452
          Login failed for user ".  The user is not associated with a trusted SQL server connecton.
 
NOTE: I am not using a trusted connection.
After the error message I get a logon box where I input my SQL username and password.  This is what I am trying to avoid.

I used your LinkTableDAO function on the 'OnOpen' event of the form that displays when the .accdb opens.  All I had to do is re-link one of the tables in the SQL DB.
Now the application opens without giving me an error or asking for the SQL username and password.
This is a 'brute force' method that works, but I am hoping for something a bit more elegant.  Got any ideas?

There is no way to store the user name and password with the linked table?

Not that I know of.
I've done quite a few Access/SQL applications for clients.  I always used the procedure of connecting the SQL tables when the program opened and disconnection when closed.  You could also add code to disconnect any linked SQL tables on openning of the program (prior to running the connect code) in case an orderly close did not occur.

You rock!  Thanks!