Home > IT Answers > Database > Write Recordset to MSAccess(mdb) using VB6.0
Nikhildhar
35 pts.
 Write Recordset to MSAccess(mdb) using VB6.0
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
ASKED: August 26, 2009  2:51 PM
UPDATED: August 28, 2009  9:45 PM
RELATED QUESTIONS
Answer Wiki:
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
Last Wiki Answer Submitted:  August 27, 2009  2:44 pm  by  Randym   1,740 pts.
All Answer Wiki Contributors:  Randym   1,740 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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.

Nikhildhar
 35 pts.
 

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]

Randym
 1,740 pts.
 

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.

Randym
 1,740 pts.