Changing from Access BE to SQLServer or MySQL on the fly

Ray

Hi folks,

I have a database that goes to a client for evaluation/purchase as an
Access2k2FE / Access2k2 BE. When they decide to upsize to MySQL or SQL
Server, I would like an easy way to allow them to switch table
sources.

When I try to use Linked Table Manager, and the Find a File dialog
pops up, it only gives me the option to look for .mdb or *.*. I would
have thought it could allow me to choose an ODBC data source, but it
doesnt appear to do so.

So instead I can use File / Get External Data / Link tables, except
the obvious drawback is that it's a complicated manual process, and I
don't want clients to have to do this.

I can code the RefreshLinks sample to do it all (I have versions that
do Access refreshes or SQLServer refreshes, with the ODBC string and
all), but will it allow me to use and swap the same TableDef connect
string for either the Access version of the table or the 'upsized'
SQLServer version of the same table?

As usual, about this time in the resolution of the problem I think
that lot's of colleagues must have been down this road already - so
any suggestions or alternative solutions would be much appreciated.

Ray

Nov 13 '05 #1

Douglas J. Steele

If you know what the Connect property should be in each case, and you know
which tables need to be connected, you can loop through the TableDefs
collection, deleting each existing linked table and adding a new one.
Doesn't matter whether it's to Jet backend or using ODBC.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Ray" <ra*@aic.net.au> wrote in message
news:9f**************************@posting.google.c om...

Hi folks,

I have a database that goes to a client for evaluation/purchase as an
Access2k2FE / Access2k2 BE. When they decide to upsize to MySQL or SQL
Server, I would like an easy way to allow them to switch table
sources.

When I try to use Linked Table Manager, and the Find a File dialog
pops up, it only gives me the option to look for .mdb or *.*. I would
have thought it could allow me to choose an ODBC data source, but it
doesnt appear to do so.

So instead I can use File / Get External Data / Link tables, except
the obvious drawback is that it's a complicated manual process, and I
don't want clients to have to do this.

I can code the RefreshLinks sample to do it all (I have versions that
do Access refreshes or SQLServer refreshes, with the ODBC string and
all), but will it allow me to use and swap the same TableDef connect
string for either the Access version of the table or the 'upsized'
SQLServer version of the same table?

As usual, about this time in the resolution of the problem I think
that lot's of colleagues must have been down this road already - so
any suggestions or alternative solutions would be much appreciated.

Ray

Nov 13 '05 #2

Tony Toews

ra*@aic.net.au (Ray) wrote:

I can code the RefreshLinks sample to do it all (I have versions that
do Access refreshes or SQLServer refreshes, with the ODBC string and
all), but will it allow me to use and swap the same TableDef connect
string for either the Access version of the table or the 'upsized'
SQLServer version of the same table?

Yes, I've done this in the past although I never finished the upsizing process on
that database.

1) I much prefer DSN-Less connections as it is one less thing for someone to have to
configure and one less thing for the users to screw up. This is also better for
Citrix/TS farms where each individual system would have to have a DSN created and
maintained.

Using DSN-Less Connections
http://members.rogers.com/douglas.j....LessLinks.html
ODBC DSN-Less Connection Tutorial Part I
http://www.amazecreations.com/datafa...m&Article=true
HOWTO: Use "DSN-Less" ODBC Connections with RDO and DAO
http://support.microsoft.com/?id=147875
ODBC DSN Less
http://www.able-consulting.com/MDAC/...BC_DSNLess.htm

2) The SQL Server tables will have dbo. as the table name. You can strip that out
when creating the Access table name.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

Nov 13 '05 #3