Switching between multiple dsn-less connections

[Paul Mitchell]

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 LinkTableDSNLess()
'Deletes a table and relinks it if it exists and links a table if it
doesnt exist
On Error Resume Next
DoCmd.DeleteObject acTable, "dbo_<mytablename>"
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect As String

sConnect = "ODBC;DRIVER={SQL Server};" & _
"SERVER=<myserver>;" & _
"DATABASE=<mydatabase>;" & _
"UID=<myuid>;" & _
"PWD=<mypwed>tprdrbc;" & ";"

Set db = CurrentDb()
Set tdf = db.CreateTableDef("dbo_<mytablename>")
tdf.SourceTableName = "dbo.<mytablename>"

tdf.Connect = sConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh

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

[rkc]

"Paul Mitchell" <pa**@marysvillenow.com> wrote in message
news:d5**************************@posting.google.c om...

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 LinkTableDSNLess()
'Deletes a table and relinks it if it exists and links a table if it
doesnt exist
On Error Resume Next
DoCmd.DeleteObject acTable, "dbo_<mytablename>"
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect As String

sConnect = "ODBC;DRIVER={SQL Server};" & _
"SERVER=<myserver>;" & _
"DATABASE=<mydatabase>;" & _
"UID=<myuid>;" & _
"PWD=<mypwed>tprdrbc;" & ";"

Set db = CurrentDb()
Set tdf = db.CreateTableDef("dbo_<mytablename>")
tdf.SourceTableName = "dbo.<mytablename>"

tdf.Connect = sConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh

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.

What version of Access?
Have you considered binding the forms to an ADODB recordset instead of
deleting and creating links?

[Paul Mitchell]

"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message news:<_b*****************@twister.nyroc.rr.com>...

"Paul Mitchell" <pa**@marysvillenow.com> wrote in message
news:d5**************************@posting.google.c om...

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 LinkTableDSNLess()
'Deletes a table and relinks it if it exists and links a table if it
doesnt exist
On Error Resume Next
DoCmd.DeleteObject acTable, "dbo_<mytablename>"
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim sConnect As String

sConnect = "ODBC;DRIVER={SQL Server};" & _
"SERVER=<myserver>;" & _
"DATABASE=<mydatabase>;" & _
"UID=<myuid>;" & _
"PWD=<mypwed>tprdrbc;" & ";"

Set db = CurrentDb()
Set tdf = db.CreateTableDef("dbo_<mytablename>")
tdf.SourceTableName = "dbo.<mytablename>"

tdf.Connect = sConnect

db.TableDefs.Append tdf
db.TableDefs.Refresh

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.

What version of Access?
Have you considered binding the forms to an ADODB recordset instead of
deleting and creating links?

I'm using Access 2000. I'm basically using ODBC because it's already
being used in the individual front ends and I know they currently
work. Additionally, Secondly these tools will probably only be around
for another 30 days or so. Last and the biggest reason: I don't
understand ADODB at all. I could probably get by with some type of
tabledef refresh but I think the datasets on the back end will change
a couple of times so it'll have to be a delete relink, refresh set-up.
Thanks for your response by the way...got me thinking about some other
things.