Solved

Grab a table from an SQL server

Posted on 2009-05-18
11
439 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now