473,231 Members | 1,743 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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
2 1298
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Stormblade | last post by:
Hey all, I have a web app that uses SQLServer 2000. I am switching to MySQL 4. 1.1. I have re-created all the tables but I'm running into 2 problems. 1. In SQLServer I can create a...
0
by: Paradigm | last post by:
I am using Access 2K as a front end to MySQL running on a Linux server. I am having trouble connectiong to the server. MySQL control centre connects and I can connect using a DSN data source. But...
6
by: Paradigm | last post by:
I am using Access 2K as a front end to MySQL running on a Linux server. I am having trouble connectiong to the server. MySQL control centre connects and I can connect using a DSN data source. But...
1
by: com | last post by:
Extreme Web Reports 2005 - Soft30.com The wizard scans the specified MS Access database and records information such as report names, parameters and subqueries. ......
1
by: Rosny | last post by:
From the article http://software.newsforge.com/software/05/01/27/170244.shtml?tid=132&tid=75&tid=131&tid=13 "Flickr runs on MySQL, the most popular open source database, and it has from the...
2
by: Ian Baker | last post by:
We have developed an Access/Jet database (2000, XP & 2003 versions) that has been used by clients all around the world for several years and is extremely robust with 55 tables, 172 hard stored...
26
by: codercode | last post by:
I'm working on a Visual Basic .NET using Access database. However, my client already have a 30MB database with Sybase ASA and Sybase is way too much for that. Is there anyway I can migrate from a...
22
by: teejayem | last post by:
Hi, I am new to programming with databases and was wanting some help. Is there any way to password protect an access database and access sent sql commands to it via vb.net code? Any help...
0
by: thatsrohit | last post by:
Hi , I need One Help I have one database(bibleGallery) which is in sql server2000. with eight tables 1) tblArtists, 2) tblBasePrice, 3) tblcategories, 4) tblPricegroup, 5) ...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.