SQL Exporting from Access to SQL 7

[PeterMac]

Is there any way to have access generate SQL commands based on the data in one of its tables, for transfer to SQL 7, or can SQL 7 import data from the .mbd file? We are doing data entry into an access table but eventually want to move it to SQL 7...

 

[Cagliostro]

You can use DTS packajes. Also, you may use SQLServer Import/Export wizard. John Fill

ivfmd@mail.md

 

[PeterMac]

Let me clarify that the implementation of SQL7 will be web server based, and I have no idea how to import an access database into a SQL 7 web based implementation. I thought I could do it through straight SQL commands and run the scripts on the server somehow, but if there is an import feature that will work through a web interface then that is wonderful. Do you think it would work in this situation?

 

[tlbroadbent]

I'm not sure what you mean by "web server based" implementation. SQL Server runs as a service, independently of your web server.

Are you familiar with Enterprise Manager (EM)? You should be able to access SQL Server with EM. From there you can start the Import/Export Wizard or create a DTS package. These tools can create the import from Access into SQL Server tables.

You can also export from Access to SQL Server if you prefer. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Amiel]

There are two ways to accomplish this (aside from the use of DTS)

The first is to export the data from MS Access (source) to SQL Server (destination).

First create a DNS (Data Source Name) using the ODBC manager, typically found under 'My Computer'/'Control Panel'. Give the DNS a descriptive name that will be easy to remember .

Then, from MS Access select the 'tables' tab. Higlight the table you wish to export, right click the mouse and click,'Save as/Export' (or from the File menu click on 'Save as Export'), and then;

click the appropriate response to the first dialog. When you get to the dialog entitled, &quot;Save Table <your_tablename> In&quot;, Use the 'drop down' with the text &quot;Save as type&quot; at the bottom of that dialog...

...the last item in that dropdown list is, &quot;ODBC Databases()&quot;. Click on this and after being presented with another dialog with which to select the destination tables name, you will be presented with the standard &quot;Select Data Source&quot; dialog, that many Access users and developers know so well.

At this point you will select your SQL Server DSN from the list. The export process begins with out further prompts.

...whew !

The second method is to import the data into SQL Server from SQL Server (using the import wizard). This too is a series of dialogs,and is usually available (with the standard installation of SQL Server) from the start menu. No kidding.

Which is to say that the developers at MS have already provided a solution for exactly the situation you've mentioned. ;-)

More ? let me know.


Amiel
amielzz@netscape.net