MS Access Database with External Data - Pass-Thorugh/select data source?

Author

Pass-Thorugh/select data source?

rocky

2005-09-15, 1:24 pm

I have a backend database. I use a pass-through query to retrive the data
from the backend. The problem that I have is that is asks for me to select
the data source every time the queries are excuted. Is there a way around
this?

Joe Fallon

2005-09-19, 8:25 pm

Use code to define the connect string and include dbAttachSavePWD.

e.g. I use this code to link tables but similar code works with SPT queries.

Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
tdfAccess.Connect = dbsODBC.Connect
---------------------------------------------------------------------------------

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_LinkSQLServerTab
les

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=passwo
rd;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_LinkSQLServerTa
bles:
SysCmd acSysCmdClearStatus
Exit Sub

Err_LinkSQLServerTab
les:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_LinkSQLServerTa
bles

End Sub

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

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_DeleteODBCTable
Names:
Exit Sub

Err_DeleteODBCTableN
ames:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
& Chr(13) & Err.Description)
Resume Exit_DeleteODBCTable
Names

End Sub

--
Joe Fallon
Access MVP

"rocky" <rocky@discussions.microsoft.com> wrote in message
news:BD9ED84E-EDEA-4843-8420- 1433343E841B@microso
ft.com...
>I have a backend database. I use a pass-through query to retrive the data
> from the backend. The problem that I have is that is asks for me to select
> the data source every time the queries are excuted. Is there a way around
> this?

rocky

2005-09-20, 7:24 am

Thanks Joe I will give this a try!! Thanks again!

"Joe Fallon" wrote:

> Use code to define the connect string and include dbAttachSavePWD.
>
> e.g. I use this code to link tables but similar code works with SPT queries.
>
> Set tdfAccess = dbs.CreateTableDef(rs![LinkTablename], dbAttachSavePWD)
> tdfAccess.Connect = dbsODBC.Connect
> ---------------------------------------------------------------------------------
>
> 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_LinkSQLServerTab
les
>
> 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=passwo
rd;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_LinkSQLServerTa
bles:
> SysCmd acSysCmdClearStatus
> Exit Sub
>
> Err_LinkSQLServerTab
les:
> MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
> & Chr(13) & Err.Description)
> Resume Exit_LinkSQLServerTa
bles
>
> End Sub
>
>
> 'This procedure deletes all linked ODBC table names in an mdb.
> Public Sub DeleteODBCTableNames
()
> On Error GoTo Err_DeleteODBCTableN
ames
>
> 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_DeleteODBCTable
Names:
> Exit Sub
>
> Err_DeleteODBCTableN
ames:
> MsgBox ("Error # " & Str(Err.Number) & " was generated by " & Err.Source
> & Chr(13) & Err.Description)
> Resume Exit_DeleteODBCTable
Names
>
> End Sub
>
> --
> Joe Fallon
> Access MVP
>
>
>
> "rocky" <rocky@discussions.microsoft.com> wrote in message
> news:BD9ED84E-EDEA-4843-8420- 1433343E841B@microso
ft.com...
>
>
>

Sponsored Links