Recordset to Table

[Luis M]

Hi,

I'm looking for the fastest way to copy a recordset (created from other BD)
to a table in my Access BD.

Thanks

Luis

 

[Pieter Wijnen]

A recordset can only by Appended using a VBA loop AFAIK
Select statements / Resultsets should be possible by using this kind of
technique

SELECT A.* INTO
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[Tables#txt]
FROM [ODBC;Driver={SQL
Server};SERVER=AAAA;DATABASE=BBBB;UID=CCCC;Pwd=DDDD;].TABLES A;

Note
Both Source & Dest are non-Access in the sample (and won't give the expected
format for the text file #?¤%@# -without a schema.ini file in the dest
folder actually specifying TextDelimiter & Format)

HTH

Pieter

 

[Dirk Goldgar]

Hi,

I'm looking for the fastest way to copy a recordset (created from
other BD) to a table in my Access BD.

The fastest way would probably have been to not use a recordset at all,
but execute a single append query to select the desired records from the
input table (in the other database) and append them to the local output
table. But it sounds as if this may not be an option.

If you're just given the recordset, already created and opened as an
object, I don't think you have much recourse but to loop through the
recordset and write each row to the local table. How you do that
writing might make a difference. You could either (A) have a second
recordset open on the output table, and copy each record from one
recordset to the other, like this:

'----- method A air code -----
Dim rsInput As DAO.Recordset
Dim rsOutput As DAO.Recordset
Dim i As Integer

' ... some how, we are given rsInput ...

' Open empty recordset on the output table.
Set rsOutput = CurrentDb.OpenRecordset( _
"SELECT * FROM OutputTable WHERE False")

With rsInput
Do Until .EOF
rsOutput.AddNew
For i = 0 To (.Fields.Count - 1)
rsOutput.Fields(i).Value = .Fields(i).Value
'*** NOTE - might need to add a test to skip
'*** an autonumber field.
Next i
rsOutput.Update
.MoveNext
Loop
End With

rsOutput.Close
rsInput.Close ' maybe
'----- end method A -----

Or (B) you could use a one-record append query inside the recordset
loop:

'----- method B air code -----
Dim rsInput As DAO.Recordset
Dim db As DAO.Database

' ... some how, we are given rsInput ...

Set db = CurrentDb

With rsInput
Do Until .EOF
db.Execute _
"INSERT INTO OutputTable (<list of fields in table>) "
"VALUES(" & _
... various field values extracted from rsInput ...
& _
")", dbFailOnError
.MoveNext
Loop
End With

rsInput.Close ' maybe
'----- end method B -----

I don't know which of these would be faster.

 

[LuisM]

Pieter, I appreciate your suggestion, although is not clear for me how to
perform this statement to my own case.

Regards

Luis

"Pieter Wijnen"

A recordset can only by Appended using a VBA loop AFAIK
Select statements / Resultsets should be possible by using this kind of
technique

SELECT A.* INTO
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[Tables#txt]
FROM [ODBC;Driver={SQL
Server};SERVER=AAAA;DATABASE=BBBB;UID=CCCC;Pwd=DDDD;].TABLES A;

Note
Both Source & Dest are non-Access in the sample (and won't give the
expected format for the text file #?¤%@# -without a schema.ini file in the
dest folder actually specifying TextDelimiter & Format)

HTH

Pieter

Hi,

I'm looking for the fastest way to copy a recordset (created from other
BD) to a table in my Access BD.

Thanks

Luis

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

 

[LuisM]

Dirk, I'll go to try with the first option that you gave me (what happens if
I use the updatebatch methot whit this option?.. it's possible?) The second
one is what I had thought but maybe slower than others, processing records
one by one.

Regards

Luis

 

[Dirk Goldgar]

Dirk, I'll go to try with the first option that you gave me (what
happens if I use the updatebatch methot whit this option?.. it's
possible?)

The code example I gave was for DAO recordsets, which don't have an
UpdateBatch method. If you're using ADO, it will be slightly different.
I use mostly DAO for working in Access, as it's more powerful, even if
it is older. Although I've done some work with ADO, I haven't done
batch updates, so I don't know any more about the UpdateBatch method
than I read in the help file.

The second one is what I had thought but maybe slower than
others, processing records one by one.

You're processing records one by one on the input side, anyway, so I'm
not sure it makes a lot of difference. If this copy process is
something that is going to run over and over again, I think it would be
worth your time to try both methods, and time them. But then, if it's
going to run over and over again, revising the whole process so that you
just run a single append query from the source table to the target table
starts to sound better than either of these methods.

 

[Pieter Wijnen]

What it does
It Reads data from a ODBC compliant Database (in this case MSSQL) & places
it in an ISAM Database (in this case a text file)
In your case you'd probably want something like
INSERT INTO MyTable (Field1,.., Fieldn)
SELECT A.Field1,..,A.Fieldn
FROM [ODBC;DSN=MyDSN;UID=MyUserId;Pwd=MyPwd].RemoteTable A

Which requires a ODBC Datasource to have been created on your machine
pointing to the remote DB

see http://www.thuleeng.com/msaccess/ for more about DSN & connections

Pieter

Pieter, I appreciate your suggestion, although is not clear for me how to
perform this statement to my own case.

Regards

Luis

"Pieter Wijnen"

A recordset can only by Appended using a VBA loop AFAIK
Select statements / Resultsets should be possible by using this kind of
technique

SELECT A.* INTO
[Text;Database=C:\Temp\;TextDelimiter=none;ColNameHeader=True;Format=TabDelimited;].[Tables#txt]
FROM [ODBC;Driver={SQL
Server};SERVER=AAAA;DATABASE=BBBB;UID=CCCC;Pwd=DDDD;].TABLES A;

Note
Both Source & Dest are non-Access in the sample (and won't give the
expected format for the text file #?¤%@# -without a schema.ini file in
the dest folder actually specifying TextDelimiter & Format)

HTH

Pieter

Hi,

I'm looking for the fastest way to copy a recordset (created from other
BD) to a table in my Access BD.

Thanks

Luis

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

 

[Jamie Collins]

The code example I gave was for DAO recordsets, which don't have an
UpdateBatch method. If you're using ADO, it will be slightly different.

A variation on you suggestion could be to use the ADO Recordset's
GetString method to write a derived table and use this to do a bulk
insert in one hit e.g.

INSERT INTO Test (col1, col2)
SELECT DT1.col1, DT1.col2
FROM (
SELECT DISTINCT 1 AS col1, 1 AS col2
FROM Test
UNION ALL
SELECT DISTINCT 2, 2
FROM Test
UNION ALL
SELECT DISTINCT 3, 3
FROM Test
) AS DT1;

Here's a demo:

Sub fabr()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe1.mdb"
With .ActiveConnection

' Create auxilary table of digits 0-9
.Execute _
"CREATE TABLE Test (" & _
" col1 INTEGER NOT NULL," & _
" col2 INTEGER NOT NULL);"

.Execute _
"INSERT INTO Test VALUES (99, 99);"

' Fabricate a recordset
Dim rsTemp
Set rsTemp = CreateObject("ADODB.Recordset")
With rsTemp
.Fields.Append "col1", adInteger
.Fields.Append "col2", adInteger
.Open
.AddNew Array("col1", "col2"), Array(1, 1)
.AddNew Array("col1", "col2"), Array(2, 2)
.AddNew Array("col1", "col2"), Array(3, 3)

.MoveFirst

Dim sql As String
sql = _
"INSERT INTO Test (col1, col2)" & _
" SELECT DT1.col1, DT1.col2 FROM (" & _
" SELECT DISTINCT NULL AS col1, " & _
" NULL AS col2 FROM Test WHERE 0=1" & _
" UNION ALL SELECT DISTINCT "

Const DELIMITER As String = _
" FROM Test UNION ALL SELECT DISTINCT "

sql = sql & .GetString(2, , ", ", DELIMITER)

' remove trailing delimiter text
sql = _
Left$(sql, Len(sql) - Len(" UNION ALL SELECT DISTINCT "))

sql = sql & _
") AS DT1;"
End With

MsgBox sql
.Execute sql

Dim rs
Set rs = .Execute( _
"SELECT col1, col2 FROM Test;")
MsgBox rs.GetString

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[Dirk Goldgar]

A variation on you suggestion could be to use the ADO Recordset's
GetString method to write a derived table and use this to do a bulk
insert in one hit e.g.

INSERT INTO Test (col1, col2)
SELECT DT1.col1, DT1.col2
FROM (
SELECT DISTINCT 1 AS col1, 1 AS col2
FROM Test
UNION ALL
SELECT DISTINCT 2, 2
FROM Test
UNION ALL
SELECT DISTINCT 3, 3
FROM Test
) AS DT1;

Here's a demo:

Sub fabr()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe1.mdb"
With .ActiveConnection

' Create auxilary table of digits 0-9
.Execute _
"CREATE TABLE Test (" & _
" col1 INTEGER NOT NULL," & _
" col2 INTEGER NOT NULL);"

.Execute _
"INSERT INTO Test VALUES (99, 99);"

' Fabricate a recordset
Dim rsTemp
Set rsTemp = CreateObject("ADODB.Recordset")
With rsTemp
.Fields.Append "col1", adInteger
.Fields.Append "col2", adInteger
.Open
.AddNew Array("col1", "col2"), Array(1, 1)
.AddNew Array("col1", "col2"), Array(2, 2)
.AddNew Array("col1", "col2"), Array(3, 3)

.MoveFirst

Dim sql As String
sql = _
"INSERT INTO Test (col1, col2)" & _
" SELECT DT1.col1, DT1.col2 FROM (" & _
" SELECT DISTINCT NULL AS col1, " & _
" NULL AS col2 FROM Test WHERE 0=1" & _
" UNION ALL SELECT DISTINCT "

Const DELIMITER As String = _
" FROM Test UNION ALL SELECT DISTINCT "

sql = sql & .GetString(2, , ", ", DELIMITER)

' remove trailing delimiter text
sql = _
Left$(sql, Len(sql) - Len(" UNION ALL SELECT DISTINCT "))

sql = sql & _
") AS DT1;"
End With

MsgBox sql
.Execute sql

Dim rs
Set rs = .Execute( _
"SELECT col1, col2 FROM Test;")
MsgBox rs.GetString

End With
Set .ActiveConnection = Nothing
End With
End Sub

That looks really interesting, and it exploits ADO features I'm not
familiar with. I may post back with a question or two.