Hello Eveyone,

           I wanted some help on exporting tables from MS Access 2000 to SQL (Oracle 9) in VBA (Excel). I have a table created in access and i export the data in this table to a table in SQL. How can i do it..? And what are the guidelines to be followed before the exporting is to be done...?

Another of my problems is.... I have managed to connect to my database in SQL using the Microsoft DAO 3.6 Object Library. The problem is that it executes the addNew method for the recordset but gives an error on execution of the update method. Here's my code for connecting to the database :-

'Set db = OpenDatabase("lwt", False, False, "ODBC;UID=fstahl;PWD=fstahl;DSN=lwt")
'Set rs_linux = db.OpenRecordset("select * from m_linux_collateral_details", dbOpenDynaset)

And Here's the code by which i am updating the fields into the database :-

rs_linux.AddNew 'This line executes correctly
rs_linux(0) = Cells(start_rows, Title).Value
rs_linux(1) = Cells(start_rows, author).Value
rs_linux(2) = Cells(start_rows, coll_type).Value
rs_linux(3) = Cells(start_rows, geo).Value
rs_linux(4) = Cells(start_rows, no).Value
MsgBox Len(Cells(start_rows, Abstract).Value)
rs_linux(5) = Cells(start_rows, Abstract).Value
rs_linux(6) = Cells(start_rows, link).Value
rs_linux.Update 'An Error is thrown up on execution of this statement
MsgBox "Title Updated", vbInformation

The error is "ODBC - Call Failed"...And all this happens in VBA for Excel not in VB

Can anybody pitch in....? Thanks....

'Try it making direct connection to sql sever.
'cording to coonect direcly is like this
' make a reference to MS ADO 2.6 Librery.
public con as new adodb.connection
public recordSet1 as adodb.recordset
con.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=username;PWD=password;Initial Catalog=databasename;Data Source=servername"
con.Open

'u can add new record using sql commands
con.excecute("Insert tablename values (valulist)")


'Get a record set
set recordSet1=con.excecute("select * from tablename")

'Accress to a field
recordSet(0) '1st colum

Hey Chamin,

         Thanks a lot for your great and timely help.....The connection string you sent didn't work as it was....i just had to put in another OLEDB Provider (Microsoft ODBC for Oracle) and Lo!!!!!!! i was able to do it....Also the table i had to declare it as adOpenDynamic and adLockOptimistic....so as to make the updates to the table possible...Anywayz...Chamin Thanks for the help....

Bye,
Raghav Shenoy.