reading and loading data into sql

[nelco]

I am not very good at vb script. I am asked to write vb script to read and load the access data base into sql database.

I will appreciate if any one can guide me or have an example so that I can start working on it.
Thanks

 

[strebor]

I'm not real familiar with this, but I think there is an "Upsizing Wizard" in MS Access database utilities that can be used to convert Access databases to MS SQL. Might be a good starting point rather than what might be a real challenge using vbscript.

Hope this helps.

strebor

 

[nelco]

Thanks Strebor for good suggestion. My problem is that I will have to read and insert data into three different sql database.
Access database is divided into three sql database.
Any suggestions welcome. I am able to connect to access database. Connection is succesful. Now I will have to read recordset and then insert into sql daabase.
so far the code I have written is:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objconn = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConn.connectionString ="Provider = Microsoft.Jet.OLEDB.4.0;Data Source= C:\Documents and Settings\moparikh\Desktop/kardex.mdb "
objconn.open
Set rsData = CreateObject("ADODB.Recordset")
rsData.ActiveConnection = objConn.connectionString
'rsData.Open "data",objconn,adOpenStatic,adLockOptimistic
rsData.Source = "select * from kardex"
rsData.CursorType = 3
rsData.CursorLocation = 3
rsData.LockType = 1
rsData.Open()

msg = "connected and opened the file"
MsgBox msg

I am getting the message.

Any suggestions or example will be very much appreciated.

 

[strebor]

If I'm understanding this... you have data in three tables in an Access database that you want to insert or add to three corresponding tables in an existing SQL database?

You might try this link to get more information:

http://support.microsoft.com/kb/315661/en-us

strebor