dynamic odbc connection

[petersm...@yahoo.de]

Hello,

I'm developing an access database that connects via ODBC to an oracle
database. So far so good. My problem is: I'm using the identical
database to connect to an test database and to an production database
- so I've to have to use two mdb files (one connects to test and the
other one to production).

Is it possible to set the odbc-connection dynamicly, so I've only one
one database (e.g. at program-start one selects wether to connect to
test or to production) ?

Best regards
Pete

[Alex Dybenko]

Hi,
yes, you can implement code to relink tables/queries based on following:
http://www.mvps.org/access/tables/tbl0010.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

<petersm...@yahoo.de> wrote in message
news:dc046bcc-73de-417c...@h2g2000yqg.googlegroups.com...

[Mary Chipman [MSFT]]

In addition to the solution Alex suggested, a simpler method might be
to create a startup form that prompts the user for the database to
connect to. You then have code that specifies the correct connection
string information and relinks the tables. Using ODBC connection
strings is preferred over DSNs--you can get valid Oracle connection
strings at connectionstrings.com (or by searching). Your code to link
individual tables would look something like the following. You'd write
code to loop through the TableDefs collection, deleting and relinking
tables as appropriate.

Public Function CreateTableDef( _
connectionString As String, _
sourceTableName As String, _
tableDefName As String) As Integer

Set db = CurrentDb

On Error Resume Next
Dim tdf As DAO.TableDef
Set tdf = db.CreateTableDef(tableDefName)
tdf.Connect = connectionString
tdf.sourceTableName = sourceTableName

db.TableDefs.Append tdf
db.TableDefs.Refresh

CreateTableDef = (Err = 0)
End Function

--Mary

On Thu, 28 May 2009 00:25:47 -0700 (PDT), petersm...@yahoo.de
wrote: