Migration tool Access 2003->2000/SQL Express

[msch-prv]

This is a trivial question but most posts I have looked up always
assume that a working copy of Access 2003 is available, which is not my
case. (I use Office 2K and don't plan to upgrade.)

I would like to convert a 2003 database down to A2K. The db acts as BE
and contains only tables; so the conversion should be straightforward.

Is there a tool out there that does that sort of conversion? I tried
stpworks at http://www.stpworks.com for exporting data in .csv but it
isn't working correctly.

If no such tool is available, would it be possible to migrate the 2003
db to SQL Express? If yes, how? My understanding is that this can only
be done natively ie. you need Access 2003 to either convert back to A2k
or upwards to MS SQL/SQL Express.

Thanks for any suggestions, Mark

 

[Sylvain Lafontaine]

A wild guess that I say without any thinking: did you try creating an empty
MDB file and linking or importing all the tables from the old one using ODBC
linked tables?

 

[dbahooker]

or instead of linking; how about just right-click IMPORT

 

[msch-prv]

Sylvain, thanks for the suggestion.

I finally created an ASP script that uses an OBDC connection to pull
out data from each table and load it in a text file. Each text file is
then formatted in Excel and imported back into A2K. What a nightmare!

Why doesn't MS make such tools available?

Regards, Mark

 

[msch-prv]

Sylvain, thanks for the suggestion.

I finally created an ASP script that uses an OBDC connection to pull
out data from each table and load it in a text file. Each text file is
then formatted in Excel and imported back into A2K. What a nightmare!

Why doesn't MS make such tools available?

Regards, Mark

 

[dbahooker]

RIGHT-CLICK IMPORT WAS ONE STEP!!!

what do you mean Microsoft doesn't make a tool for this?

for the record, MDB is completely and utterly obsolete; if you're
building databases in the year 2006; you SHOULD be using SQL Server.

 

[msch-prv]

Well, this is confusing. Are we talking about importing A2003 mdbs into
SQL Express or A2K?

As far as I know (and re-tested), you cannot import a 2003 table into
A2K. A2K flags you with an error message.

If you refer to SQL Express, then I don't know of a way to import mdb.
The ASP2.0 starter kit I'm using enables you to create tables manually.
Is there a separate mdb/SQL Express import utility?

Mark

 

[John Vinson]

Well, this is confusing. Are we talking about importing A2003 mdbs into
SQL Express or A2K?

I think you'll find that dbahooker is more than a bit fanatical about
this subject. He never posts here other than to bash Jet databases and
all of us who are successfully using them; I'd review some of his
previous posts using groups.google.com before taking his advice to
heart.


John W. Vinson[MVP]

 

[Sylvain Lafontaine]

Well, I don't have a copy of Access 2000 at hand so I'm not sure of the
following; however, if you can pull your data from ASP then you should be
able to do exactly the same with VBA code and ADO objects directly from
inside your Access file.

Also, I don't understand why you don't try to create linked tables to
directly get access to the data. (Or use the syntaxe IN ). Linked tables
are based on ODBC, so you shouldn't have any problem to link tables between
the A2000 and A2003 formats.

They are many ways and tools to transfert data; however, I think that you
mention of SQL-Express as a way of downgrading the format of the MDB
database from A2003 to A2000 is confusing everyone here on what exactly you
want to do.

 

[Sylvain Lafontaine]

I've just checked on MSDN and Access 2000 or Office 2000 are no longer
available; so I cannot install them to make a quick check.

If you are not really interested in copying your data to SQL-Express and
that you still have trouble with ODBC linked tables or with calling ADO
objects from VBA inside Access, I would suggest that you make a repost but
without any mention of SQL-Express.

MS is making some tools, like DTS (for SQL-Server 2000) or SSIS (for
SQL-Server 2005/Express); however it would be far more easier for you to
stick with ODBC linked tables or ADO objects.

 

[Sylvain Lafontaine]

One final thought: as you don't have Access 2003 installed, then maybe
installing the latest version of MDAC and after that the latest of the JET
drivers could help you:

http://msdn.microsoft.com/data/ref/mdac/downloads/

 

[dbahooker]

ok so you make a new db in version 2003; make it format = 2000

and then push it using access 2003 into the access 2000 mdb that you
created with access 2003.

then you have a format 2000 database with all the tables.

Right?

 

[dbahooker]

John;

i'm so sorry that you're a flaming MDB wimp.

the advice i gave works on my machine.

Yes; I hate MDB.

you so called MDB kids; you sit there and spread BLATANT LIES about ADP
and you consistently discourage people from using a real database.

SQL Server is free, MDB is completely obsolete.

-Aaron