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!
leodavidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CJ_SCommented:
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
leodavidAuthor Commented:
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
__Holly__Commented:
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
CJ_SCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thandava VallepalliCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.