Copying Tables from Recordset

[neoartz237]

I want to copy a table from Oracle through ADODB Connection.
I did an early draft of of it but its runs so slow.
I have 11 tables: Here's my code:

Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim tblNames(1 To 11) As String
Dim newName, InsStr, strModifier, connStr As String
Dim i, c, d As Integer

'////Fill up tables array////
tblNames(1) = "TKYOKUMD01"
tblNames(2) = "TTOKUKMD01"
tblNames(3) = "TTOKUIMD01"
tblNames(4) = "TSIHARMD01"
tblNames(5) = "TTORIKMD01"
tblNames(6) = "TSERMEMD01"
tblNames(7) = "TKANKAMD01"
tblNames(8) = "TSWKMSTD01"
tblNames(9) = "TUKKMKMD01"
tblNames(10) = "TCALENMD01"
tblNames(11) = "TSEKYUMD01"

For i = 1 To 11
If doChecker(tblNames(i)) Then
Set cmd = Nothing
Set rst = Nothing
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
conn.Open restorConStr '<<<----- check mdlConnection Module
DoCmd.RunSQL ("DELETE * FROM " & tblNames(i))
cmd.ActiveConnection = conn
cmd.CommandType = adCmdTable
cmd.CommandText = tblNames(i)
Set rst = cmd.Execute
If rst.RecordCount <> 0 Then
Do While Not rst.EOF
InsStr = ""
c = rst.Fields.Count
For d = 0 To c - 1
'////Check if Numeric or not////
If rst.Fields(d).Type = adNumeric Then
If Len(rst.Fields(d).Value) > 255 Then
strModifier = Mid(rst.Fields(d).Value, 1, 255)
Else
strModifier = rst.Fields(d).Value
End If

Else
If Len(rst.Fields(d).Value) > 255 Then
strModifier = "'" & Mid(rst.Fields(d).Value, 1, 255) & "'"
Else
strModifier = "'" & rst.Fields(d).Value & "'"
End If
End If

If d <> c - 1 Then
InsStr = InsStr & strModifier & ","
Else
InsStr = InsStr & strModifier
End If
Next d
DoCmd.RunSQL ("INSERT INTO " & tblNames(i) & " VALUES (" & InsStr & ")")
rst.MoveNext
Loop
End If
End If
Next i
Set rst = Nothing


Now, it works perfectly and runs no errors, but it takes almost 10 minutes to complete. I figure, if I can copy the tables from the recordset since that I declared it as table in :
"cmd.CommandType = adCmdTable
md.CommandText = tblNames(i)
Set rst = cmd.Execute"

Maybe I can copy it then use INSERT SELECT because as we all know thats much more efficient and faster. Please help

 

[MicroE]

You can save the Record Set as a XM file, then you can import the XML file into MS Access. I have had issues trying to execute an Insert statement that is created by looping thought the recordset. Certain values in the fields within the Oracle table (such as periods, single quotes, double quotes) prevent the SQL from executing properly.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthsavex.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthrstsave.asp

http://msdn2.microsoft.com/en-us/library/aa189302(office.10).aspx

 

[neoartz237]

You can save the Record Set as a XM file, then you can import the XML file into MS Access. I have had issues trying to execute an Insert statement that is created by looping thought the recordset. Certain values in the fields within the Oracle table (such as periods, single quotes, double quotes) prevent the SQL from executing properly.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthsavex.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthrstsave.asp

http://msdn2.microsoft.com/en-us/library/aa189302(office.10).aspx

Cool, except, umm how do I import the data? The power of Google points me to Menu based, can I do it programmatically? Thanks

 

[WayneRyan]

Neo,

An alternative method would be to create a link to the table in Access.
You can do that programmatically, or through: File --> Get External Information --> Link Tables.
It would probably be easier and faster.

Then, your entire code would be:

DoCmd.RunSql "Drop Table YourCopiedTable"
DoCmd.RunSql "Select * Into YourCopiedTable From YourLinkedTable"
DoCmd.RunSql "Drop Table YourLinkedTable"

Search for "DSN" and "Linked", you should find some examples.

Wayne

 

[neoartz237]

Neo,

An alternative method would be to create a link to the table in Access.
You can do that programmatically, or through: File --> Get External Information --> Link Tables.
It would probably be easier and faster.

Then, your entire code would be:

DoCmd.RunSql "Drop Table YourCopiedTable"
DoCmd.RunSql "Select * Into YourCopiedTable From YourLinkedTable"
DoCmd.RunSql "Drop Table YourLinkedTable"

Search for "DSN" and "Linked", you should find some examples.

Wayne

Umm through ODBC right? Well, I dunno why but the customer's have a bad blood or something with ODBC, they dont like it, that been said.. I have to use ADODB

Thats why I have no choice but to do old school and go individual on each records, ....making the process ridiculously slow

I've even tried changing the connection of the linked tables to ADODB but it fires and error.

Can I link tables to access' interface through ADODB? Please help

Oh and also, they want the connections to be done with UDLs, which I did in that first approach

Question is, can I actually link tables through connections from UDL files to Access Interface? Many thanks

 

[MicroE]

Here is a MS Article on Saving as XML then Importing the XML.
http://support.microsoft.com/kb/285329/

 

[neoartz237]

Here is a MS Article on Saving as XML then Importing the XML.
http://support.microsoft.com/kb/285329/

I cant thank you enough heheh thanks

 

[neoartz237]

Oh God!! Apparently, you can only use the Application.ImportXML for ACCESS Version 2002 and above, but Im using the relic, 2000. I guess Im back to square 1

If I set my recordset in a way that it acts like a table, like what I coded above. Can I make a clone of this in Access?.....