Solved

MS Access to MS SQL

Posted on 2003-11-10
7
606 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 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

22 Experts available now in Live!

Get 1:1 Help Now