Plugging an alternate data source into MS-Access

[Chad]

Hi

Is it possible to substitute an alternative data source (eg MySQL or
SQL Server) into an existing MS-Access application?

[Tom van Stiphout]

On 26 Mar 2007 05:54:43 -0700, "Chad" <ch********@hotmail.comwrote:

Yes. That's what linked tables and passthrough queries are for. Read
about them in the Help file.

-Tom.

>Hi

Is it possible to substitute an alternative data source (eg MySQL or
SQL Server) into an existing MS-Access application?

['69 Camaro]

Hi, Chad.

Is it possible to substitute an alternative data source (eg MySQL or
SQL Server) into an existing MS-Access application?

Yes. Ensure that your workstation has the appropriate driver installed for
the database engine you choose, then you may:

1.) Link to the tables with the Linked Table Manager using a DSN (Data
Source Name) or DSN-less connection; or

2.) Write SQL Passthrough Queries in the database engine's native SQL
dialect; or

3.) Write VBA code using ADO or DAO libraries to connect to the tables and
manipulate the data.

The most efficient is the SQL Passthrough Queries, and the least efficient
is recordsets using ADO or DAO libraries, with or without table links.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Chad" <ch********@hotmail.comwrote in message
news:11**********************@l77g2000hsb.googlegr oups.com...

Hi

Is it possible to substitute an alternative data source (eg MySQL or
SQL Server) into an existing MS-Access application?

[Chad]

Thanks Guys

Wasn't aware of the linking feature and have just had a look at
linking of alternative ODBC data sources. I don't know if it will help
me. I'll explain the problem:

A client has a very large legacy Access database and has put a lot of
work into the forms side of things. They want to migrate to a
different database format (eg MySQL). Migrating the data is a problem
but I think that can be handled with a really big script. However,
heres the problem: the user will want to use the OLD forms application
(largely unchanged) after the data has been migrated. This using of
the old forms stuff would be until a new UI is developed in the target
platform.

So I was hoping for something that would enable me to use the existing
forms with the new data source. I was hoping I could make a single
global change to seamlessly point the old forms stuff at the new data.
However I don't think that will be possible with linking.

When I link a table in from MySQL I would need to change the forms
code to use the linked in table explicitly. I was hoping the MySQL
tables could all have the same names as the original tables.

Would the other two options be worth pursuing to help solve this
problem?
Is there a nice clean API between the forms and the data source?
Perhaps the forms are stored in text format and I could change the
data source in there?

Much obliged,
Chad.

[Don Leverton]

Hi Chad,
I have never done *exactly* what you are doing here ...
but I DO know that you can changed the name of a linked table right in the
database window -- without affecting the name of the "real" table.

I had a parts inventory application (I have since changed it a lot) where I
used to link "tblInventory" from several identically-structured MDB files
.... one for each customer.
I linked to, and then simply renamed each customer's "tblInventory" to
"FountainTire", or "Tirecraft", etc. Doing so allowed me to differentiate
between these linked inventory tables, changing the names only in this app.

To sum this up ... link to your MySQL tables, and then just rename the links
in your Access front-end to whatever table names you had originally.
Let us know if it works ... I can't see why it won't.

HTH,
Don
"Chad" <ch********@hotmail.comwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...

Thanks Guys

Wasn't aware of the linking feature and have just had a look at
linking of alternative ODBC data sources. I don't know if it will help
me. I'll explain the problem:

A client has a very large legacy Access database and has put a lot of
work into the forms side of things. They want to migrate to a
different database format (eg MySQL). Migrating the data is a problem
but I think that can be handled with a really big script. However,
heres the problem: the user will want to use the OLD forms application
(largely unchanged) after the data has been migrated. This using of
the old forms stuff would be until a new UI is developed in the target
platform.

So I was hoping for something that would enable me to use the existing
forms with the new data source. I was hoping I could make a single
global change to seamlessly point the old forms stuff at the new data.
However I don't think that will be possible with linking.

When I link a table in from MySQL I would need to change the forms
code to use the linked in table explicitly. I was hoping the MySQL
tables could all have the same names as the original tables.

Would the other two options be worth pursuing to help solve this
problem?
Is there a nice clean API between the forms and the data source?
Perhaps the forms are stored in text format and I could change the
data source in there?

Much obliged,
Chad.

['69 Camaro]

Hi, Chad.

Wasn't aware of the linking feature . . .
I don't know if it will help me. . . .
Migrating the data is a problem
but I think that can be handled with a really big script. . . .
So I was hoping for something that . . .
I was hoping I could make a single
global change . . .
I was hoping the MySQL
tables could all have the same names as the original tables. . . .
Would the other two options be worth pursuing to help solve this
problem? . . .
Is there a nice clean API between the forms and the data source? . . .
Perhaps the forms are stored in text format and I could change the
data source in there? . . .

This is going to sound harsh, because it is harsh. If you have time before
this project's deadline, take some Access database courses or get tutoring
from an Access database expert. If you don't have time, please find an
Access database developer to do the migration, because a successful
migration with Access as the front end requires someone who has some
knowledge of, and experience with, Access. Failed Access database
applications are almost always blamed on Access's shortcomings, not the true
guilty party -- inexperience.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"Chad" <ch********@hotmail.comwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...

Thanks Guys

Wasn't aware of the linking feature and have just had a look at
linking of alternative ODBC data sources. I don't know if it will help
me. I'll explain the problem:

A client has a very large legacy Access database and has put a lot of
work into the forms side of things. They want to migrate to a
different database format (eg MySQL). Migrating the data is a problem
but I think that can be handled with a really big script. However,
heres the problem: the user will want to use the OLD forms application
(largely unchanged) after the data has been migrated. This using of
the old forms stuff would be until a new UI is developed in the target
platform.

So I was hoping for something that would enable me to use the existing
forms with the new data source. I was hoping I could make a single
global change to seamlessly point the old forms stuff at the new data.
However I don't think that will be possible with linking.

When I link a table in from MySQL I would need to change the forms
code to use the linked in table explicitly. I was hoping the MySQL
tables could all have the same names as the original tables.

Would the other two options be worth pursuing to help solve this
problem?
Is there a nice clean API between the forms and the data source?
Perhaps the forms are stored in text format and I could change the
data source in there?

Much obliged,
Chad.

[AlterEgo]

Chad,

There are three words that do not pertain to the task of migrating from
Access to another database:
global, seamlessly and clean.

Globally, you can handle about 90% of the migration. Of course, its the
other 10% that will cause the grief. The following is just an example of
what you will be dealing with:

1. Datatypes are not the same between Access and other SQL databases. Watch
out for date/time datatypes among others.
2. Autonumber are handled differently.
3. You may have objects named as reserved words in the new DB which will
cause it to fail.

Just be aware the project will more than likely be much larger than you are
expecting. Make sure you do a lot of QA to find the things that are broken.
Microsoft has a good write-up on converting to SQL Server. The issues
described in the write-up are comparable to the issues you will run into
with other databases such as MySQL.

http://www.microsoft.com/technet/pro...migration.mspx

MS also has an upgrade utility for SQL Server. You might want to consider
SQL Server Express as the target database.

-- Bill

"Chad" <ch********@hotmail.comwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...

Thanks Guys

Wasn't aware of the linking feature and have just had a look at
linking of alternative ODBC data sources. I don't know if it will help
me. I'll explain the problem:

A client has a very large legacy Access database and has put a lot of
work into the forms side of things. They want to migrate to a
different database format (eg MySQL). Migrating the data is a problem
but I think that can be handled with a really big script. However,
heres the problem: the user will want to use the OLD forms application
(largely unchanged) after the data has been migrated. This using of
the old forms stuff would be until a new UI is developed in the target
platform.

So I was hoping for something that would enable me to use the existing
forms with the new data source. I was hoping I could make a single
global change to seamlessly point the old forms stuff at the new data.
However I don't think that will be possible with linking.

When I link a table in from MySQL I would need to change the forms
code to use the linked in table explicitly. I was hoping the MySQL
tables could all have the same names as the original tables.

Would the other two options be worth pursuing to help solve this
problem?
Is there a nice clean API between the forms and the data source?
Perhaps the forms are stored in text format and I could change the
data source in there?

Much obliged,
Chad.

[Chad]

Spot on thank you Don and Bill.
Indeed I had just read this article moments before you had replied. It
makes for depressing reading. I would readily concur with your
assertion that the project is quite large and contains many pitfalls.
That said I shall not balk! I have come across another useful link
which presents three options and their associated increasing costs:

See: http://www.aldex.co.uk/sqlupsizing.html

Where they categorize:
-Basic Migration £3,000 (What we have been talking about here i.e.
Upsizing).
-Pragmatic Conversion £10,000-£15,000 (Migrating more stuff to the
server, stored procedures for example)
-Full Redesign ......?

I would reckon the Basic Migration may perhaps be a tad under priced
in the above given the QA required. I may consider one of the other
options either. The customer has made it fairly clear that their
schema is a bit of a mess!

Again thank you. I will perhaps sign off now until I have more hands
on experience of the system. Any more technical appreciated.

On Mar 26, 7:03 pm, "AlterEgo" <[e-mail]:

Chad,

There are three words that do not pertain to the task of migrating from
Access to another database:
global, seamlessly and clean.

Globally, you can handle about 90% of the migration. Of course, its the
other 10% that will cause the grief. The following is just an example of
what you will be dealing with:

1. Datatypes are not the same between Access and other SQL databases. Watch
out for date/time datatypes among others.
2. Autonumber are handled differently.
3. You may have objects named as reserved words in the new DB which will
cause it to fail.

Just be aware the project will more than likely be much larger than you are
expecting. Make sure you do a lot of QA to find the things that are broken.
Microsoft has a good write-up on converting to SQL Server. The issues
described in the write-up are comparable to the issues you will run into
with other databases such as MySQL.

http://www.microsoft.com/technet/pro...eploy/accessmi...

MS also has an upgrade utility for SQL Server. You might want to consider
SQL Server Express as the target database.

-- Bill

"Chad" <[e-mail] in message

news:11**********************@e65g2000hsc.googlegr oups.com...

Thanks Guys

Wasn't aware of the linking feature and have just had a look at
linking of alternative ODBC data sources. I don't know if it will help
me. I'll explain the problem:

A client has a very large legacy Access database and has put a lot of
work into the forms side of things. They want to migrate to a
different database format (eg MySQL). Migrating the data is a problem
but I think that can be handled with a really big script. However,
heres the problem: the user will want to use the OLD forms application
(largely unchanged) after the data has been migrated. This using of
the old forms stuff would be until a new UI is developed in the target
platform.

So I was hoping for something that would enable me to use the existing
forms with the new data source. I was hoping I could make a single
global change to seamlessly point the old forms stuff at the new data.
However I don't think that will be possible with linking.

When I link a table in from MySQL I would need to change the forms
code to use the linked in table explicitly. I was hoping the MySQL
tables could all have the same names as the original tables.

Would the other two options be worth pursuing to help solve this
problem?
Is there a nice clean API between the forms and the data source?
Perhaps the forms are stored in text format and I could change the
data source in there?

Much obliged,
Chad.

[G. Miller]

Just a little FYI, but we recently changed vendor software and are now
running our new Advertising system on a Oracle 9i database, and it is
a large database. We are using Access to link to those tables for
reporting purposes...at least the guy sitting next to me is...and he
is no Access expert.

So, it is possible.
On 26 Mar 2007 09:21:00 -0700, "Chad" <ch********@hotmail.comwrote:

>Thanks Guys

Wasn't aware of the linking feature and have just had a look at
linking of alternative ODBC data sources. I don't know if it will help
me. I'll explain the problem:

A client has a very large legacy Access database and has put a lot of
work into the forms side of things. They want to migrate to a
different database format (eg MySQL). Migrating the data is a problem
but I think that can be handled with a really big script. However,
heres the problem: the user will want to use the OLD forms application
(largely unchanged) after the data has been migrated. This using of
the old forms stuff would be until a new UI is developed in the target
platform.

So I was hoping for something that would enable me to use the existing
forms with the new data source. I was hoping I could make a single
global change to seamlessly point the old forms stuff at the new data.
However I don't think that will be possible with linking.

When I link a table in from MySQL I would need to change the forms
code to use the linked in table explicitly. I was hoping the MySQL
tables could all have the same names as the original tables.

Would the other two options be worth pursuing to help solve this
problem?
Is there a nice clean API between the forms and the data source?
Perhaps the forms are stored in text format and I could change the
data source in there?

Much obliged,
Chad.

[AlterEgo]

G.,

Linking to an existing database is quite easy. What Chad is talking about is
converting an existing Access database with non-standard datatypes,
non-standard SQL, etc. - it can get hairy.

-- Bill

"G. Miller" <gm*****@nospam-mobileregister.comwrote in message
news:46****************@newsgroups.bellsouth.net.. .

Just a little FYI, but we recently changed vendor software and are now
running our new Advertising system on a Oracle 9i database, and it is
a large database. We are using Access to link to those tables for
reporting purposes...at least the guy sitting next to me is...and he
is no Access expert.

So, it is possible.
On 26 Mar 2007 09:21:00 -0700, "Chad" <ch********@hotmail.comwrote:

>>Thanks Guys

Wasn't aware of the linking feature and have just had a look at
linking of alternative ODBC data sources. I don't know if it will help
me. I'll explain the problem:

A client has a very large legacy Access database and has put a lot of
work into the forms side of things. They want to migrate to a
different database format (eg MySQL). Migrating the data is a problem
but I think that can be handled with a really big script. However,
heres the problem: the user will want to use the OLD forms application
(largely unchanged) after the data has been migrated. This using of
the old forms stuff would be until a new UI is developed in the target
platform.

So I was hoping for something that would enable me to use the existing
forms with the new data source. I was hoping I could make a single
global change to seamlessly point the old forms stuff at the new data.
However I don't think that will be possible with linking.

When I link a table in from MySQL I would need to change the forms
code to use the linked in table explicitly. I was hoping the MySQL
tables could all have the same names as the original tables.

Would the other two options be worth pursuing to help solve this
problem?
Is there a nice clean API between the forms and the data source?
Perhaps the forms are stored in text format and I could change the
data source in there?

Much obliged,
Chad.

[AlterEgo]

Chad,

.... customer has made it fairly clear that their schema is a bit of a mess
.... RUN AWAY FAST! Just kidding. If you are doing this under contract, don't
quote a fixed price, quote time and materials. If you do it under a fixed
contract, you'll be fixing the conversion and all of the bugs that are
already in the application.

-- Bill
"Chad" <ch********@hotmail.comwrote in message
news:11*********************@b75g2000hsg.googlegro ups.com...
Spot on thank you Don and Bill.
Indeed I had just read this article moments before you had replied. It
makes for depressing reading. I would readily concur with your
assertion that the project is quite large and contains many pitfalls.
That said I shall not balk! I have come across another useful link
which presents three options and their associated increasing costs:

See: http://www.aldex.co.uk/sqlupsizing.html

Where they categorize:
-Basic Migration £3,000 (What we have been talking about here i.e.
Upsizing).
-Pragmatic Conversion £10,000-£15,000 (Migrating more stuff to the
server, stored procedures for example)
-Full Redesign ......?

I would reckon the Basic Migration may perhaps be a tad under priced
in the above given the QA required. I may consider one of the other
options either. The customer has made it fairly clear that their
schema is a bit of a mess!

Again thank you. I will perhaps sign off now until I have more hands
on experience of the system. Any more technical appreciated.

On Mar 26, 7:03 pm, "AlterEgo" <[e-mail]:

Chad,

There are three words that do not pertain to the task of migrating from
Access to another database:
global, seamlessly and clean.

Globally, you can handle about 90% of the migration. Of course, its the
other 10% that will cause the grief. The following is just an example of
what you will be dealing with:

1. Datatypes are not the same between Access and other SQL databases.
Watch
out for date/time datatypes among others.
2. Autonumber are handled differently.
3. You may have objects named as reserved words in the new DB which will
cause it to fail.

Just be aware the project will more than likely be much larger than you
are
expecting. Make sure you do a lot of QA to find the things that are
broken.
Microsoft has a good write-up on converting to SQL Server. The issues
described in the write-up are comparable to the issues you will run into
with other databases such as MySQL.

http://www.microsoft.com/technet/pro...eploy/accessmi...

MS also has an upgrade utility for SQL Server. You might want to consider
SQL Server Express as the target database.

-- Bill

"Chad" <[e-mail] in message

news:11**********************@e65g2000hsc.googlegr oups.com...

Thanks Guys

Wasn't aware of the linking feature and have just had a look at
linking of alternative ODBC data sources. I don't know if it will help
me. I'll explain the problem:

A client has a very large legacy Access database and has put a lot of
work into the forms side of things. They want to migrate to a
different database format (eg MySQL). Migrating the data is a problem
but I think that can be handled with a really big script. However,
heres the problem: the user will want to use the OLD forms application
(largely unchanged) after the data has been migrated. This using of
the old forms stuff would be until a new UI is developed in the target
platform.

So I was hoping for something that would enable me to use the existing
forms with the new data source. I was hoping I could make a single
global change to seamlessly point the old forms stuff at the new data.
However I don't think that will be possible with linking.

When I link a table in from MySQL I would need to change the forms
code to use the linked in table explicitly. I was hoping the MySQL
tables could all have the same names as the original tables.

Would the other two options be worth pursuing to help solve this
problem?
Is there a nice clean API between the forms and the data source?
Perhaps the forms are stored in text format and I could change the
data source in there?

Much obliged,
Chad.

[Chad]

Yep, I would concur with your advice. We have agreed a daily rate.
I am hoping however that the basic migration with no rearchirecture
makes it in under two person weeks as its only the beginning of a
major peice of work.

... customer has made it fairly clear that their schema is a bit of a mess
... RUN AWAY FAST! Just kidding. If you are doing this under contract, don't
quote a fixed price, quote time and materials.