"rkc" <rkc@yabba.dabba.do.rochester.rr.bomb> wrote in message news:<_bTic.4695$X14.4248@twister.nyroc.rr.com>...[color=blue]
> "Paul Mitchell" <paul@marysvillenow.com> wrote in message
> news:d52111d7.0404241406.58a15a5a@posting.google.c om...[color=green]
> > 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.[/color]
>
> What version of Access?
> Have you considered binding the forms to an ADODB recordset instead of
> deleting and creating links?[/color]
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.
