Solved

MS Access to MS SQL

Posted on 2003-11-10
7
607 Views
Last Modified: 2008-03-17
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!
0
Comment
Question by:leodavid
7 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 9713579
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.
0
 

Author Comment

by:leodavid
ID: 9713805
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.
0
 
LVL 1

Expert Comment

by:__Holly__
ID: 9719065
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?
0
 
LVL 22

Accepted Solution

by:
CJ_S earned 50 total points
ID: 9720580
Through an ASP interface you will need to do the following:

- read all system tables from the Access database. Tables include: MSysAccessObjects, MSysAces, MSysObjects, MSysQueries, MSysrelationships.
Those tables also contain the names of other tables which you can then query. DO a select * and join with the right relationships. With that information you can create the CREATE TABLE statement and easily loop through all data within the recordset. Please note that it won't be easy and that it is much easier to do it from the enterprise manager.

In ASP you can loop through a recordset and all fields using:

set rs = ....
If(Not rs.eof and Not rs.bof) Then
 DO While Not rs.eof
   For each f in rs.fields
     Response.write f.name & " - " & rs(f)
   Next
   rs.MoveNext
 Loop
Else

End If
rs.Close
Set rs = Nothing

Please also note that processing may take a long time and thus a timeout can occur!
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 9721611
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.




0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now