asked on

MS Access to MS SQL

If this question has already been posted, PLEASE redirect me to it. But if not, here's my question:

Is it possible to export a MS Access to MS SQL using ASP (or ADO?) script? What the script basically does is read the tablenames and all fieldnames in each tablename and records from the MDB and dynamically create the same table name and fieldname & fieldtype to the MS SQL dbase. Replication? =)

Please help!

Thanks!

CJ_S

You can import your tables and data in a SQL Server database. All you need is access to the SQL Server. You can find the import export wizard when you rightclick on the database -> All task -> import data.

leodavid

ASKER

hi CJ_S,

I was thinking exporting it via the web browser coz i have a form and a script where it uploads the MS ACCESS (.mdb) to the web server, an ***ASP script*** replicates it to MS SQL -- reads all the MDB tablename and fieldname and creates the same tablenames and fieldnames with fieldtypes dynamically to the MS SQL.

I can easily do it via the wizard but i need the ***ASP script***.

Thanks for trying tho

=)

> FROM CJ_S
> You can import your tables and data in a SQL Server database. All you need is access to
> the SQL Server. You can find the import export wizard when you rightclick on the
> database -> All task -> import data.

__Holly__

uh you could do this with DAO if you knew what you were doing.

or even the access object model.

it just depends on exactly what you are doing.

why would you have someone upload an mdb? wouldnt it be easier to upload just the CSVs you needed?

ASKER CERTIFIED SOLUTION

CJ_S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Thandava Vallepalli

Hi,

Do one thing.

First time you manually do DTS import/Export Wizard. In last step in this wizard (i.e Save, Schedule, and replicate pacakge window) check 'Save DTS Package' check box and select 'Visual Basic File' and then click next. It will ask you enter the package name description file path. After entering all finally click finish button.

Now open this file (.BAS) in VB Environment. This is DTS programming. If you go through the code properly, you will be finding that password information of SQLServer will not be stored in .BAS file for some security purpose. Set the correct values to the missed fields. Now make it .EXE file. Clear SQLServer tables for testing. Run this exe file again. And check the tables, whether the tables are populated or not.

Now convert this file into component (just copy this code paste in component (ActiveX DLL) and change the 'MAIN' function name to some meaning full function name. That it. )

Use this component in ASP file, after uploading Access file. Send the path of this MDB file to this component. It will transfer the tables to the sqlserver.

Same thing i did reverse using this approch. I transfered SQLServer to Access and mailed access file to administrator.

All the best,
V.Thandava Krishna.