Write Recordset to MSAccess(mdb) using VB6.0
Home > IT Answers > Database > Write Recordset to MSAccess(mdb) using VB6.0
Nikhildhar
35 pts.
0
Q:
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: Aug 26 2009  2:51 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Randym
1410 pts.
0
A:
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
  • AddThis Social Bookmark Button
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 Answered: Aug 27 2009  2:44 PM GMT by Randym   1410 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Nikhildhar   35 pts.  |   Aug 28 2009  1:23PM GMT

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.

 

Randym   1410 pts.  |   Aug 28 2009  9:32PM GMT

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   1410 pts.  |   Aug 28 2009  9:45PM GMT

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.

 
0