Re: Automatically logging in to SQL Server (w/Access Front End)
From: Joe Fallon (jfallon1_at_nospamtwcny.rr.com)
Date: 03/13/04
- Next message: TC: "Re: VBA and Access"
- Previous message: jp: "VBA and Access"
- In reply to: Anthony Cravero: "Automatically logging in to SQL Server (w/Access Front End)"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 12 Mar 2004 23:28:10 -0500
Not sure if this works for 2 connections but....
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
"Anthony Cravero" <NOSPAM@nospam.com> wrote in message
news:%23galD2GCEHA.1600@tk2msftngp13.phx.gbl...
> Hi all,
>
> I have a very short snippit of code that I use to connect to a database in
> the background, preventing the users from having to enter an ID. My
problem
> is this--I now have linked/global tables for 2 ODBC connections to SQL
> servers. The code works fine with one, but the second continuously
flashes
> the SQL Server login screen anytime it tries to access on of the tables in
> the 2nd db. I would like this whole process to be transparent to the
users,
> if possible (as it used to be with 1 connection).
>
> Here is the code I am currently using:
>
> Function EstConn()
> Dim db1 As DAO.DATABASE, db2 As DAO.DATABASE
> Dim dbcurr As DAO.DATABASE
>
> Set dbcurr = CurrentDb()
> Set db1 = OpenDatabase("", False, False, _
> "ODBC;DSN=dbName1;" & _
> "UID=readonly;PWD=password1;DATABASE=" & _
> "dbName1;")
> Set db2 = OpenDatabase("", False, False, _
> "ODBC;DSN=dsnName2;" & _
> "UID=readonly2;PWD=password2;DATABASE=" & _
> "dbName2;")
> End Function
>
> Is there another way to force a connection? When I try to "save" settings
> in my ODBC connections, it still prompts me for username & PW no matter
what
> I do.
>
> Thank you,
> Anthony
>
>
- Next message: TC: "Re: VBA and Access"
- Previous message: jp: "VBA and Access"
- In reply to: Anthony Cravero: "Automatically logging in to SQL Server (w/Access Front End)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
