Re: Plugging an alternate data source into MS-Access

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" <altereg...@xxxxxxxxxxxxxx> wrote:
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/prodtechnol/sql/2000/deploy/accessmi...

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

-- Bill

"Chad" <chadzak...@xxxxxxxxxxx> wrote in message

news:1174926060.231309.211630@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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.


.

Relevant Pages

  • Starting with VB2005 and SQL Server 2008 Express Edition
    ... Writing a VB application to connect to SQL Server 2008 Express to handle the data entry instead of using Access. ... However I'm finding it difficult to try out SQL Server 2008 Express to connect to a database from VB. ... When I click this it opens a window that asks me to choose a data source type. ... The next screen asks me to choose a data connection. ...
    (microsoft.public.vb.database)
  • Re: Using of sql server for back end database
    ... 'file data source' window of the upsizing wizard. ... If you are refering to Upsizing your back end to the SQL Server, ... From the Menu at the top select Database Utilities... ...
    (microsoft.public.access.queries)
  • Re: Plugging an alternate data source into MS-Access
    ... you can handle about 90% of the migration. ... Microsoft has a good write-up on converting to SQL Server. ... SQL Server Express as the target database. ... Is there a nice clean API between the forms and the data source? ...
    (comp.databases.ms-access)
  • How to migrate SQL 2000 stored procedures and UDFs to VS 2005?
    ... VS 2003 + SQL Server 2005 ... The migration went smoothly, and the application and database are working ... I have 100+ stored procedures and UDF's inside my database. ... Manage stored procedures and UDF's inside VSS ...
    (microsoft.public.vstudio.development)
  • Re: Copy a Datbase
    ... are going to have to write some manual code to build the schema and populate ... A web service to retrieve the data source will be ... Sql Server 2000 code will not directly translate to Sql Server CE! ... If you are just doing a lookup of the database, ...
    (microsoft.public.sqlserver.ce)
Loading