[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Grab a table from an SQL server

Posted on 2009-05-18
11
Medium Priority
?
485 Views
Last Modified: 2013-11-27
HELLLOOO Experts!

I need to "grab" a table from an SQL server programaticly.  (No wizards)
Also, I can't use a DSN file.  The front-end I am making (for an SQL back-end) will be distributed and needs to be "self-contained".

So: How can I connect to a table on an SQL server and have it show up on my list of linked tables in my .accdb file?

Thanks!
0
Comment
Question by:AviationAce
  • 5
  • 4
  • 2
11 Comments
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24413209
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

Open in new window

0
 
LVL 4

Expert Comment

by:TimSledge
ID: 24414594
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

Open in new window

0
 

Author Comment

by:AviationAce
ID: 24414835

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

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 4

Expert Comment

by:TimSledge
ID: 24414888
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.
0
 
LVL 14

Expert Comment

by:RDWaibel
ID: 24415356
Tim's method is the best way attach the table.
0
 

Author Comment

by:AviationAce
ID: 24415580
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.
0
 

Author Comment

by:AviationAce
ID: 24415976
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?
0
 
LVL 4

Accepted Solution

by:
TimSledge earned 2000 total points
ID: 24416653
It's probably trying to open the previous link, but no longer has the SQL username and password.  Sorry, what I left out is that you need to unlink when you close the application, then re-link when you reopen.  That should solve the problem you described.

I'm attaching code for unlinking.

Public Sub subDisconnectLinkedSQLTablesAndViews()
On Error GoTo Err_subDisconnectLinkedSQLTablesAndViews
 
Dim strGetLinkedTableName As String
Dim intCountTables As Integer
 
intCountTables = 1
 
DoCmd.OpenForm "frmSQLConnectionsHidden", acNormal, , , acFormEdit, acHidden
DoCmd.GoToRecord , "frmSQLConnectionsHidden", acFirst
 
While intCountTables <= DLookup("[HowMany]", "qrySQLLinkedTablesHowMany")
 
    strGetLinkedTableName = Forms!frmSQLConnectionsHidden!SQLLinkedTablesName
 
    UnLinkTableDAO (strGetLinkedTableName)
        'MsgBox "just linked " & strGetTableName
    
    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 disconnected successfully."
  End If
 
Exit_subDisconnectLinkedSQLTablesAndViews:
    Exit Sub
 
Err_subDisconnectLinkedSQLTablesAndViews:
    MsgBox Err.Description
    Resume Exit_subDisconnectLinkedSQLTablesAndViews
    
End Sub
 
Public Function UnLinkTableDAO(strLinkName As String) As Boolean
 
 
Dim db As DAO.Database
Dim tdf As DAO.TableDef
 
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
End If
UnLinkTableDAO = (Err = 0) 'true if No Errors False if Error
 
End Function

Open in new window

0
 

Author Comment

by:AviationAce
ID: 24416830
There is no way to store the user name and password with the linked table?
0
 
LVL 4

Expert Comment

by:TimSledge
ID: 24417026
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.
0
 

Author Closing Comment

by:AviationAce
ID: 31582628
You rock!  Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question