Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 17th, 2004, 10:02 AM
Registered User
Join Date: Dec 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to raghav_shenoy Send a message via Yahoo to raghav_shenoy
Default Data Exporting from MS Access to SQL

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....
Old December 20th, 2004, 04:52 AM
Registered User
Join Date: Dec 2004
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts

'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"

'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

Old December 20th, 2004, 03:22 PM
Registered User
Join Date: Dec 2004
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to raghav_shenoy Send a message via Yahoo to raghav_shenoy

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....

Raghav Shenoy.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting Query Data From Access to Excel JimInSouthernCal Access VBA 3 December 21st, 2007 08:34 AM
Database migration MS Access 2003 to MS SQL 2000 ayazhoda SQL Server 2000 3 April 23rd, 2007 11:38 AM
MS ACCESS 2003 FRONTEND AND MS SQL SERVER 2005 DB mohankumar0709 SQL Server 2005 3 March 23rd, 2007 12:48 AM
MS Data Grid control in ms access application roshla_p Access VBA 5 October 16th, 2006 02:37 AM
Exporting data from MS Excel sheet to Ms Access ajindal General .NET 1 January 17th, 2005 03:00 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.