Link SQL table through VB Code

I have an Access97 database and I would like to link a SQL table to it
without having to create a manual ODBC connection on every system that
needs to use this database.

This is what I have so far to make the ODBC connection:

Dim DBconnect As Object
Dim CnnStr As String

CnnStr = "Provider=sqloledb;" & _
"Data Source=servername;" & _
"Initial Catalog=odbc-connection-name;" & _
"User ID=username;Password=;"

Set DBconnect = CreateObject("ADODB.Connection")
DBconnect.ConnectionString = CnnStr

This code works fine to create the connection. What I need now, is the
code to create the actual link to the "openord" table in the "proddata"
database on that SQL server.

I guess it would be a good idea to check to see if the table exists and
if it does to skip the link connection. Otherwise it may give an error
that the link is already there?

Thanks for any help !

re: Link SQL table through VB Code

Check what I have at http://www.accessmvp.com/DJSteele/DSNLessLinks.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"Bruce Lawrence" <dgr8koolaid@hotmail.com> wrote in message
news:1130277635.939362.137090@o13g2000cwo.googlegr oups.com...[color=blue]
>I have an Access97 database and I would like to link a SQL table to it
> without having to create a manual ODBC connection on every system that
> needs to use this database.
>
> This is what I have so far to make the ODBC connection:
>
> Dim DBconnect As Object
> Dim CnnStr As String
>
> CnnStr = "Provider=sqloledb;" & _
> "Data Source=servername;" & _
> "Initial Catalog=odbc-connection-name;" & _
> "User ID=username;Password=;"
>
> Set DBconnect = CreateObject("ADODB.Connection")
> DBconnect.ConnectionString = CnnStr
>
> This code works fine to create the connection. What I need now, is the
> code to create the actual link to the "openord" table in the "proddata"
> database on that SQL server.
>
> I guess it would be a good idea to check to see if the table exists and
> if it does to skip the link connection. Otherwise it may give an error
> that the link is already there?
>
> Thanks for any help !
>[/color]

re: Link SQL table through VB Code

Bruce:

The following KB articles shows how to link an external table. The sample
code shows how to check for the existence of an existing table, as well as
link to an external table. This sample code uses DAO as opposed to ADO.

http://support.microsoft.com/default...b;en-us;159691

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"Bruce Lawrence" <dgr8koolaid@hotmail.com> wrote in message
news:1130277635.939362.137090@o13g2000cwo.googlegr oups.com...
I have an Access97 database and I would like to link a SQL table to it
without having to create a manual ODBC connection on every system that
needs to use this database.

This is what I have so far to make the ODBC connection:

Dim DBconnect As Object
Dim CnnStr As String

CnnStr = "Provider=sqloledb;" & _
"Data Source=servername;" & _
"Initial Catalog=odbc-connection-name;" & _
"User ID=username;Password=;"

Set DBconnect = CreateObject("ADODB.Connection")
DBconnect.ConnectionString = CnnStr

This code works fine to create the connection. What I need now, is the
code to create the actual link to the "openord" table in the "proddata"
database on that SQL server.

I guess it would be a good idea to check to see if the table exists and
if it does to skip the link connection. Otherwise it may give an error
that the link is already there?

Thanks for any help !

re: Link SQL table through VB Code

David Lloyd wrote:[color=blue]
> Bruce:
>
> The following KB articles shows how to link an external table. The sample
> code shows how to check for the existence of an existing table, as well as
> link to an external table. This sample code uses DAO as opposed to ADO.
>
> http://support.microsoft.com/default...b;en-us;159691
>[/color]

For checking existance of a local table, this will be quicker:

(slightly modified from production code to eliminate global db variable
to make it work standalone)
Function ExistTableDef(pstrTableName As String) As Boolean
' Return true is a Tabledef exists of that name
Dim tdf As TableDef
Dim db As Database
Set db = CurrentDB()
For Each tdf In db.TableDefs
If tdf.name = pstrTableName Then
ExistTableDef = True
Exit For
End If
Next
Set tdf = Nothing
Exit Function
End Function

It's also better than working off an error, MS' example assumes that
only one error could possibly occur. If another error occurs, it will
assume the table exists.

The reason it's quicker is that it can loop the tabledefs a lot faster
than it can set up an error handler (which does take a lot of time). It
doesn't need an error handler itself as any errors in here should be
trapped by the calling process.

re: Link SQL table through VB Code

Thank you everyone for posting.

Here is what I have so far. I believe some of this came from Mr.
Steele's code. Can't remember as I've tried a gazzillion things so far
with no luck. This is what I posted at Experts Exchange.

Here is what I have so far but I'm getting errors.
__________________________________________________ __
Function CreateDSNConnection()
On Error GoTo CreateDSNConnection_Err

Dim stConnect As String
stConnect = "Driver={SQL
Server};Server=servername;Database=test;Uid=user;P wd=password;"

DBEngine.RegisterDatabase "proddata", "SQL Server", True, stConnect

CreateDSNConnection = True
Exit Function

CreateDSNConnection_Err:

CreateDSNConnection = False
MsgBox "CreateDSNConnection encountered an unexpected error: " &
Err.Description

End Function
__________________________________________________

That code right there gives me "ODBC Call failed". But I know the
username and password I'm trying works because I can create the DSN
manually. If I can manage to create the DSN, I'll manually link the
table in SQL and use this code in the autoexec or something.


Now, here is the other way I'm trying to do it.
__________________________________
Function linktbl()

Dim DBconnect As Object
Dim CnnStr As String
Dim MyDb As Database
Dim tdf As TableDef

CnnStr = "Provider=sqloledb;" & _
"Data Source=servername;" & _
"Initial Catalog=test;" & _
"User ID=user;password=password;"

Set DBconnect = CreateObject("ADODB.Connection")
DBconnect.ConnectionString = CnnStr

Set MyDb = CurrentDb

Set tdf = MyDb.CreateTableDef("openord", dbAttachSavePWD, "openord",
CnnStr)
MyDb.TableDefs.Append tdf
MyDb.TableDefs.Refresh

End Function
_____________________________________________

This code gets down to the "mydb.tabledefs.append" line and gives me an
error "Couldn't find installable ISAM".

re: Link SQL table through VB Code

I do beleive I've found the answer...

http://support.microsoft.com/kb/q159691/

Not the most glorified but it works! I'm going to try to find a way to
bypass the need for the table and store the connection info another
way.

re: Link SQL table through VB Code

I do beleive I've found the answer...

http://support.microsoft.com/kb/q159691/

Not the most glorified but it works! I'm going to try to find a way to
bypass the need for the table and store the connection info another
way.

re: Link SQL table through VB Code

Ok here is the fix... I'll post this as a seperate solution

__________________________________________________ __
Function DoesTblExist(strTblName As String) As Boolean
On Error Resume Next
Dim db As Database, tbl As TableDef
Set db = CurrentDb
Set tbl = db.TableDefs(strTblName)
If Err.Number = 3265 Then ' Item not found.
DoesTblExist = False
Exit Function
End If
DoesTblExist = True
End Function

Function CreateODBCLinkedTables() As Boolean
On Error GoTo CreateODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As Database, rs As Recordset, tbl As TableDef
' ---------------------------------------------
' Register ODBC database(s)
' ---------------------------------------------
Set db = CurrentDb
DBEngine.RegisterDatabase "dbname", _
"SQL Server", _
True, _
"Description=Production Data" & _
Chr(13) & "Server=myservername" & _
Chr(13) & "Database=SQLdbName"
' ---------------------------------------------
' Link table
' ---------------------------------------------
strTblName = "openord"
strConn = "ODBC;"
strConn = strConn & "DSN=proddata;"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=test;"
strConn = strConn & "UID=username;"
strConn = strConn & "PWD=password;"
strConn = strConn & "TABLE=openord"
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, ("openord"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If

CreateODBCLinkedTables = True
MsgBox "Refreshed ODBC Data Sources", vbInformation
CreateODBCLinkedTables_End:
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, "MyApp"
Resume CreateODBCLinkedTables_End
End Function
________________________________________________