Solved

Grab a table from an SQL server

Posted on 2009-05-18
11
433 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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
Tim's method is the best way attach the table.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:AviationAce
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
There is no way to store the user name and password with the linked table?
0
 
LVL 4

Expert Comment

by:TimSledge
Comment Utility
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
Comment Utility
You rock!  Thanks!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

744 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

16 Experts available now in Live!

Get 1:1 Help Now