Solved

MS Access to MS SQL

Posted on 2003-11-10
7
610 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

739 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