I have taken several individual apps that each use a dsn and combined
them into one app. I was wondering if anyone has any sample code that
would allow switching between odbc dsn-less connections to multile
dbs, with and without Trusted NT? the following function works but I
can't figure out the logic to deploy it. Essentially the databases and
tables would change based on what form is opened. Currently the
approach I've used is create a table--> create a form-->create a dsn
connection-->create an autoexec function that sets the dsn up on the
users machine.
I have successfully deployed an app using the following dsn-less
connection and I like this approach but I'm thinking some kind of "IIF
this form is opened from my switchboard then use this
connection-->when this form closes close this connection" approach
might work. I do have a couple of forms that would use the same
connection as well. I'm open to any suggestions.
Function LinkTableDSNLes s()
'Deletes a table and relinks it if it exists and links a table if it
doesnt exist
On Error Resume Next
DoCmd.DeleteObj ect acTable, "dbo_<mytablena me>"
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect As String
sConnect = "ODBC;DRIVER={S QL Server};" & _
"SERVER=<myserv er>;" & _
"DATABASE=<myda tabase>;" & _
"UID=<myuid >;" & _
"PWD=<mypwed>tp rdrbc;" & ";"
Set db = CurrentDb()
Set tdf = db.CreateTableD ef("dbo_<mytabl ename>")
tdf.SourceTable Name = "dbo.<mytablena me>"
tdf.Connect = sConnect
db.TableDefs.Ap pend tdf
db.TableDefs.Re fresh
Set tdf = Nothing
Set db = Nothing
End Function
FYI, I only use SQL Server databases and ODBC but I'm open to other
methods if I can have some hand holding.
I would appreciate any assistance anyone could provide.
Paul
them into one app. I was wondering if anyone has any sample code that
would allow switching between odbc dsn-less connections to multile
dbs, with and without Trusted NT? the following function works but I
can't figure out the logic to deploy it. Essentially the databases and
tables would change based on what form is opened. Currently the
approach I've used is create a table--> create a form-->create a dsn
connection-->create an autoexec function that sets the dsn up on the
users machine.
I have successfully deployed an app using the following dsn-less
connection and I like this approach but I'm thinking some kind of "IIF
this form is opened from my switchboard then use this
connection-->when this form closes close this connection" approach
might work. I do have a couple of forms that would use the same
connection as well. I'm open to any suggestions.
Function LinkTableDSNLes s()
'Deletes a table and relinks it if it exists and links a table if it
doesnt exist
On Error Resume Next
DoCmd.DeleteObj ect acTable, "dbo_<mytablena me>"
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect As String
sConnect = "ODBC;DRIVER={S QL Server};" & _
"SERVER=<myserv er>;" & _
"DATABASE=<myda tabase>;" & _
"UID=<myuid >;" & _
"PWD=<mypwed>tp rdrbc;" & ";"
Set db = CurrentDb()
Set tdf = db.CreateTableD ef("dbo_<mytabl ename>")
tdf.SourceTable Name = "dbo.<mytablena me>"
tdf.Connect = sConnect
db.TableDefs.Ap pend tdf
db.TableDefs.Re fresh
Set tdf = Nothing
Set db = Nothing
End Function
FYI, I only use SQL Server databases and ODBC but I'm open to other
methods if I can have some hand holding.
I would appreciate any assistance anyone could provide.
Paul
Comment