Link ODBC Tables from SQL

  • Thread starter Thread starter Steve W
  • Start date Start date
S

Steve W

I know this is probably a rote and basic newbie question. We have modified a
few access 2000 databases that link to a SQL server. Yet, each time I try to
perform an operation it prompts me for a password. Many different people use
the database, so if I link external tables via an ODBC driver on my
computer, won't a user from another workstation have a problem linking? To
further the question, even though I am using an ODBC driver, it STILL
prompts me for a logon. Is there a way to save the password within access?
If I build in an ODBC connection in VB at form_load main menu, will that
solve the problem?
 
The first time you create an ODBC link, there is an option to save the
password (look carefully). If you are using a DSN, you can also store it
there.

Finally, you can use Windows NT authentification (also know as Windows
Integrated Security) with a Windows account mapped inside SQL-Server to a
login and totally forget about this problem.
 
I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub
 
I get a type mismatch error when I tun this script. The following code is
higlighted after the error:
Set rs = dbs.OpenRecordset("tblODBCTables")

My table name is correct: "tblODBCTables". I added a few fields, so the
table now reads:
TablePrimaryID (autonum)
TableName (text): "dbo.BatchControl", "dbo.BatchWork", etc.
DSN (text): "OGS_DEV"
UserID (text): "ogs_user"
Password (text): "ogs_user"
Database (text): "OSG_0"

Any idea why this doesn't work?



Joe Fallon said:
I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_ and it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code. The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE=" &
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub

--
Joe Fallon
Access MVP



Steve W said:
I know this is probably a rote and basic newbie question. We have modified
a
few access 2000 databases that link to a SQL server. Yet, each time I try
to
perform an operation it prompts me for a password. Many different people
use
the database, so if I link external tables via an ODBC driver on my
computer, won't a user from another workstation have a problem linking? To
further the question, even though I am using an ODBC driver, it STILL
prompts me for a logon. Is there a way to save the password within access?
If I build in an ODBC connection in VB at form_load main menu, will that
solve the problem?
Click to expand...
Click to expand...
 
Try checking your references.
There were a couple of versions of Access where the default library was ADO
instead of DAO 3.6.
Just uncheck ADO and check DAO 3.6.

Also, be sure the fieldnames in your tblODBCTables match the fieldnames in
your code.
e.g. rs![LinkTablename]

--
Joe Fallon
Access MVP



Robert_L_Ross said:
I get a type mismatch error when I tun this script. The following code is
higlighted after the error:
Set rs = dbs.OpenRecordset("tblODBCTables")

My table name is correct: "tblODBCTables". I added a few fields, so the
table now reads:
TablePrimaryID (autonum)
TableName (text): "dbo.BatchControl", "dbo.BatchWork", etc.
DSN (text): "OGS_DEV"
UserID (text): "ogs_user"
Password (text): "ogs_user"
Database (text): "OSG_0"

Any idea why this doesn't work?



Joe Fallon said:
I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_ and
it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table
names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code.
The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE="
&
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub

--
Joe Fallon
Access MVP



Steve W said:
I know this is probably a rote and basic newbie question. We have
modified
a
few access 2000 databases that link to a SQL server. Yet, each time I
try
to
perform an operation it prompts me for a password. Many different
people
use
the database, so if I link external tables via an ODBC driver on my
computer, won't a user from another workstation have a problem linking?
To
further the question, even though I am using an ODBC driver, it STILL
prompts me for a logon. Is there a way to save the password within
access?
If I build in an ODBC connection in VB at form_load main menu, will
that
solve the problem?
Click to expand...
Click to expand...
Click to expand...
 
OK, last stupid question for the week...

Where do I place this code and how do I call to it?

P.S., I checked my references and you were right, I didn't have the correct
library. I don't get the error anymore but I don't know how to call to this
function. I'm thinking for test purposes I'd like it button-driven, but
after testing proves correct, I'd like it to run at database startup.

THX!

Joe Fallon said:
Try checking your references.
There were a couple of versions of Access where the default library was ADO
instead of DAO 3.6.
Just uncheck ADO and check DAO 3.6.

Also, be sure the fieldnames in your tblODBCTables match the fieldnames in
your code.
e.g. rs![LinkTablename]

--
Joe Fallon
Access MVP



Robert_L_Ross said:
I get a type mismatch error when I tun this script. The following code is
higlighted after the error:
Set rs = dbs.OpenRecordset("tblODBCTables")

My table name is correct: "tblODBCTables". I added a few fields, so the
table now reads:
TablePrimaryID (autonum)
TableName (text): "dbo.BatchControl", "dbo.BatchWork", etc.
DSN (text): "OGS_DEV"
UserID (text): "ogs_user"
Password (text): "ogs_user"
Database (text): "OSG_0"

Any idea why this doesn't work?



Joe Fallon said:
I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_ and
it
allows you to point to different versions of the same database easily.)
There is a local Access table (tblODBCTables) that contains the table
names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the code.
The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN & ";UID=User;PWD=password;DATABASE="
&
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub

--
Joe Fallon
Access MVP



I know this is probably a rote and basic newbie question. We have
modified
a
few access 2000 databases that link to a SQL server. Yet, each time I
try
to
perform an operation it prompts me for a password. Many different
people
use
the database, so if I link external tables via an ODBC driver on my
computer, won't a user from another workstation have a problem linking?
To
further the question, even though I am using an ODBC driver, it STILL
prompts me for a logon. Is there a way to save the password within
access?
If I build in an ODBC connection in VB at form_load main menu, will
that
solve the problem?
Click to expand...
Click to expand...
Click to expand...
 
I have my code in a module.
I use a form to gather the values and then in a button on the form I pass
the values to the code.
--
Joe Fallon
Access MVP



Robert_L_Ross said:
OK, last stupid question for the week...

Where do I place this code and how do I call to it?

P.S., I checked my references and you were right, I didn't have the
correct
library. I don't get the error anymore but I don't know how to call to
this
function. I'm thinking for test purposes I'd like it button-driven, but
after testing proves correct, I'd like it to run at database startup.

THX!

Joe Fallon said:
Try checking your references.
There were a couple of versions of Access where the default library was
ADO
instead of DAO 3.6.
Just uncheck ADO and check DAO 3.6.

Also, be sure the fieldnames in your tblODBCTables match the fieldnames
in
your code.
e.g. rs![LinkTablename]

--
Joe Fallon
Access MVP



Robert_L_Ross said:
I get a type mismatch error when I tun this script. The following code
is
higlighted after the error:
Set rs = dbs.OpenRecordset("tblODBCTables")

My table name is correct: "tblODBCTables". I added a few fields, so
the
table now reads:
TablePrimaryID (autonum)
TableName (text): "dbo.BatchControl", "dbo.BatchWork", etc.
DSN (text): "OGS_DEV"
UserID (text): "ogs_user"
Password (text): "ogs_user"
Database (text): "OSG_0"

Any idea why this doesn't work?



:

I use this procedure to re-create links to SQL Server.
====================================================
For Jet re-linking code see:
http://www.mvps.org/access/tables/tbl0009.htm
====================================================
(This eliminates the need to re-name all the tables to strip out dbo_
and
it
allows you to point to different versions of the same database
easily.)
There is a local Access table (tblODBCTables) that contains the table
names
I want to link to on the Server.
Note: the source table name needs the dbo. prefix which is in the
code.
The
linked table name usualy omits this. .

Public Sub LinkSQLServerTables(strDSN As String, strDatabase)
On Error GoTo Err_LinkSQLServerTables

Dim dbs As Database, rs As Recordset, tdfAccess As TableDef
Dim dbsODBC As Database, strConnect As String

If strDSN = "" Then
MsgBox "You must supply a DSN in order to link tables."
Exit Sub
Else
strConnect = "ODBC;DSN=" & strDSN &
";UID=User;PWD=password;DATABASE="
&
strDatabase & ";"
End If

SysCmd acSysCmdSetStatus, "Connecting to SQL Server..."

Call DeleteODBCTableNames

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblODBCTables")
Set dbsODBC = OpenDatabase("", False, False, strConnect)

Do While Not rs.EOF
Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename],
dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
tdfAccess.SourceTableName = dbsODBC.TableDefs("dbo." &
rs![LinkTablename]).Name
dbs.TableDefs.Append tdfAccess
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
Set dbsODBC = Nothing
Set dbs = Nothing

Exit_LinkSQLServerTables:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTables:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTables

End Sub


'This procedure deletes all linked ODBC table names in an mdb.
Public Sub DeleteODBCTableNames()
On Error GoTo Err_DeleteODBCTableNames

Dim dbs As Database, tdf As TableDef, I As Integer
Set dbs = CurrentDb
For I = dbs.TableDefs.Count - 1 To 0 Step -1
Set tdf = dbs.TableDefs(I)
If (tdf.Attributes And dbAttachedODBC) Then
dbs.TableDefs.Delete (tdf.Name)
End If
Next I

dbs.Close
Set dbs = Nothing

Exit_DeleteODBCTableNames:
Exit Sub

Err_DeleteODBCTableNames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTableNames

End Sub

--
Joe Fallon
Access MVP



I know this is probably a rote and basic newbie question. We have
modified
a
few access 2000 databases that link to a SQL server. Yet, each time
I
try
to
perform an operation it prompts me for a password. Many different
people
use
the database, so if I link external tables via an ODBC driver on my
computer, won't a user from another workstation have a problem
linking?
To
further the question, even though I am using an ODBC driver, it
STILL
prompts me for a logon. Is there a way to save the password within
access?
If I build in an ODBC connection in VB at form_load main menu, will
that
solve the problem?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top