Exporting tables to .sql code

[Patrik Nygren]

I'm not completely new to databases, but I fail to see how you export tables,
queries etc from Access to .sql files. At this stage I don't know if I'm
going to import them into another Access database or to SQL server. I've
tried DBWscript and have been able to get some result, but I am limited to
something that's free. I would prefer a Microsoft official way if such
exists..

 

[Banana]

I'm not completely new to databases, but I fail to see how you export tables,
queries etc from Access to .sql files. At this stage I don't know if I'm
going to import them into another Access database or to SQL server. I've
tried DBWscript and have been able to get some result, but I am limited to
something that's free. I would prefer a Microsoft official way if such
exists..

Jet doesn't quite support importing/exporting to/from .sql files, though
there are 3rd party tools that may do this. I believe there's a DBImport
that offer this service but I'm sure it's a for-fee.

If you are willing to do the work, you could roll out your own solution
by writing out a VBA script to read the table structure using DAO and
generating the SQL statements and writing it to a text file, but that's
just exchanging your time for money.

 

[Albert D. Kallal]

I'm not completely new to databases, but I fail to see how you export
tables,
queries etc from Access to .sql files. At this stage I don't know if I'm
going to import them into another Access database or to SQL server. I've
tried DBWscript and have been able to get some result, but I am limited to
something that's free. I would prefer a Microsoft official way if such
exists..

It helps to know ahead of time if you plan to send the data to Oracle,
Filemaker, dbase, sql server, mYsql or <insert your favorite database here>

As general rule, the sql server tools will import the mdb file. And, if you
have access running on your desktop, then you can use the external data tab
(or export options) and pump the data straight up to sql server. So, there
good a number of choices.

There is not the ability to script out the mdb files as sql text, but most
of the mainstream databases will import mdb files anyway. If you absolute
must have to script out the data, then download the free edition of sql
server, the using the built-in upsizing tools in ms-access. You can then
simply use the sql server studio tools out script this out. So, you can do
this for free if need be.

Considering that the mdb file is a simple "one file" with all the tables in
it, it likely best to just keep/use that format for transferring around the
data. All recent editions of sql server can import mdb files, and also
ms-access can upsize or export out to sql server. So, really, it much a
waste of your time and money to send the data out to some .sql file that may
not even be compatible with the server you want the data to be consumed with
anyway...

You might want to expand on what you needs are, but for the most part the
up-sizing tools in ms-access should very much export your data up to sql
server.

However, I found that the upsizing tool here is far batter:

SQL Server Migration Assistant (SSMA) for Microsoft Access
http://www.microsoft.com/downloads/...b4-c914-4ac7-b2f3-d25fff4e24fb&displaylang=en


Note there is a edition for 2008 also, see the bottom of the above link...