Write Recordset to MSAccess(mdb) using VB6.0

35 pts.
Tags:
Microsoft Access
Visual Basic
Visual Basic 6
How can I create a new mdb file from recordset which is populated from oracle db.

Please share any examples of writing recordset into mdb file.

Thanks in advance.



Software/Hardware used:
VB6.0, MS Access 2003

Answer Wiki

Thanks. We'll let you know when a new response is added.

You would need to create a new access application object.

Private AccApp As New Access.Application
AccApp.NewCurrentDatabase (“c:\newdb.mdb”)

Then you would need to create a tabledef object and define the columns with in that tabledef.

AccApp.CreateTableDef(“NewTable”)
AccApp.tabledefs(“NewTable”).CreateField(“Col1″,[type],[size])
AccApp.tabledefs(“NewTable”).CreateField(“Col2″,[type],[size])…

Then you could populate that table from the Oracle recordset.

You could also import the Oracle table into the new Access database to eliminate the need to create the tabledef.

AccApp.DoCmd.TransferDatabase acImport, ….

Or Link the Oracle table and create a table from the linked table

AccApp.DoCmd.TransferDatabase acLink, “ODBC Database”, <ConnectString>, acTable, ,”OracleTableName”, “OracleTableName”, , True
AccApp.Docmd.RunSQL(“INSERT INTO NewTable SELECT * FROM OracleTableName”)
AccApp.DoCmd.DeleteObject acTable, “OracleTableName” ‘Delete the link when done

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Nikhildhar
    Thanks a lot Randym. It really helped me :) But still I m facing one more issue. Let me explain it with my code. This is the code I have written. Private AccApp As New Access.Application Private Sub Command1_Click() AccApp.NewCurrentDatabase ("C:TempMS_Access_DB_TestNewdb2.mdb") AccApp.DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=DSNname;UID=user;PWD=password;", acTable, "prod.quality", "Linked_Table", , True AccApp.DoCmd.RunSQL ("Select * into NewTab from Linked_Table") AccApp.DoCmd.DeleteObject acTable, "Linked_Table" AccApp.CloseCurrentDatabase End Sub This is working absolutely fine. But if i m changing TransferDatabase to AccApp.DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=DSNname;UID=user;PWD=password;", acQuery, "Select * from prod.quality where id = '102' ", "Linked_Table", , True I m getting following Error here: Run-time error '3146' ODBC--call failed. [Microsoft][ODBC driver for Oracle]Invalid string or buffer length (#0) I hope you or anyone here has any resolution for this problem. If I wont get any solution for this then i have to create a work table for the select query in oracle which i want to avoid.
    35 pointsBadges:
    report
  • Randym
    acQuery is probably not valid with acLink . You can only link tables. You could create a view in Oracle rather than a work table and link to the view. if '102' is the criteria at all times. If that is not the case, you could create a pass through query that returns records. You can create the SQL code for the pass through query and use that in the TransferDatabase method. Dim qd As QueryDef Set qd = CurrentDb().CreateQueryDef("Linked_Table") qd.sql = "select * from prod.quality where id = '102'" qd.ReturnsRecords = True qd.Connect = [ConnectString]
    1,740 pointsBadges:
    report
  • Randym
    I meant to say use the pass through query in the RunSQL statement. Not the transferdatabase method. Put the CreateQueryDef method inplace of the TransferDatabase method.
    1,740 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following