Insert from one recordset to another? 1

[jbradley]

I'm trying to insert records from an ADO recordset created from a delimited text file into another recordset connected to a physical database. I'm working in VB6, ADO 2.5 and (for now) an Access database. This code:

Set cn = New ADODB.Connection
Set rsWorkFile = New ADODB.Recordset
Set db = New ADODB.Connection
Set rsReturns = New ADODB.Recordset
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\;Extended Properties='text; HDR=NO;FMT=Delimited'"
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test_Returns.mdb;Persist Security Info=False"
rsWorkFile.Open "SELECT * FROM [WorkFile.txt]", cn, adOpenKeyset, adLockReadOnly
db.Execute "INSERT INTO returns SELECT * FROM " & rsWorkFile & ""

gives me a Type mismatch compile error. If I change the db.Execute line to:

db.Execute "INSERT INTO returns SELECT * FROM rsWorkFile"

I get an error that says the Jet engine can't find the table or query rsWorkFile.

What am I missing here?

 

[NickISD]

Hi,
The problem is your Execute, it's trying to INSERT into a table within the connected database and as returns is not a valid table name you are getting the error.

I suggest you insert the data you require from the recordset and the flat file then run a query to select the data from the temp table.

Hope this helps

Regards, Nick

 

[jbradley]

Actually "returns" is the name of the table in the Access database that I'm trying to insert the records into. It's rsWorkFile, the recordset that is being created from the flat file that isn't being found in the second Execute example.

 

[NickISD]

Sorry, see what you mean. The db.Execute "INSERT INTO returns SELECT * FROM rsWorkFile" will not work because the query is looking for a table named rsWorkFile because it is enclosed within the quotes. I don't know if it is possible to query a flat file and insert to a table in one SQL statement, it may be. I would open the file using your

rsWorkFile.Open "SELECT * FROM [WorkFile.txt]", cn, adOpenKeyset, adLockReadOnly
While Not (rsWorkFile.EOF amd rsWorkFile.BOF)
' Make up a string based on each line of data then
myText=rsWorkFile("FieldOne&quot & ","
myText = myText & rsWorkFile("FieldTwo&quot
db.Execute "INSERT INTO returns VALUES (" & myText & &quot"


rsWorkFile.MoveNext
Wend
rsWorkFile.Close

Regards, Nick

 

[jbradley]

I'm trying to get away from parsing the text file programatically and adding it to the table one string at a time because it's too slow. That's why I thought of this. I've been careful to make the rsWorkFile look identical to the physical table using a Schema.ini file to define it.

How would I go about inserting the records into the returns table if rsWorkFile were connected to an identical physical table but in a different database?

 

[NickISD]

I can't see anyway of using ADO to do this in one execute, unless someone knows different. You could however use the code below. You'll need to set a ref to MS Access 9.0 Object Library - this may well work for you.

Regards, Nick



Public Sub ImportText(FileName)
Dim Acc As New Access.Application
Dim pFileName As String, TableName As String

'***
'Select filename from X_FileName
'import to Access using import specs
'***
TableName = Left(FileName, Len(FileName) - 4)
pFileName = PathData & "" & FileName & ""
Acc.OpenCurrentDatabase ("" & PathDb & "&quot
Acc.DoCmd.TransferText acImportDelim, "WfsBranchMailings", "" & TableName & "", "" & pFileName & "", False
Acc.CloseCurrentDatabase

End Sub

 

[mmayo]

I know you said you were attempting to get away from parsing and adding one string at a time, but have you tried using disconnected recordsets? Although still not as fast as I would like, disconnecting, processing, reconnecting, and updating was still 60-70% faster then just parsing.

If interested I can include the code...

 

[jbradley]

The idea of using disconnected resordsets hadn't occurred to me but it sounds promising. I would be very interested in seeing the code.

Thanks...

 

[mmayo]

I'm always looking for a better way, so if this doesn't measure up, let me know!

strSQL = "SELECT INTXDH.Itemkey,INTXDH.Trntyp,MAX(INTXDH.Apldate) " & _
"FROM INTXDH WHERE (INTXDH.Trntyp like 'S%') " & _
&quot;AND (INTXDH.Apldate <'&quot; & Format(DateAdd(&quot;d&quot;, 1, varDate), &quot;yyyy-mm-dd&quot & &quot;') &quot; & _
&quot;GROUP BY INTXDH.Itemkey,INTXDH.Trntyp&quot;

With comMaxSales
.ActiveConnection = cn
.CommandText = strSQL
End With

Set rsMaxSales = comMaxSales.Execute()
Set rsMaxSales.ActiveConnection = Nothing

strSQL = &quot;SELECT * FROM MaxSales&quot;

Set rsAccMaxSales = New ADODB.Recordset
rsAccMaxSales.Open strSQL, cnAccess, adOpenDynamic, adLockBatchOptimistic
Set rsAccMaxSales.ActiveConnection = Nothing

Do While Not rsMaxSales.EOF
for i = 0 to rsMaxSales.fieldcount -1
rsAccMaxSales(i) = rsMaxSales(i)
next i
rsMaxSales.movenext
Loop

Set rsAccMaxSales.ActiveConnection = cnAccess
rsAccMaxSales.UpdateBatch
Set rsAccMaxSales.ActiveConnection = Nothing

 

[CCLINT]

jbradley: You were on the right track in your initial question. However, you need to use one recordset object. Set the connection to the MDB.

Then do something like the following:

cn.Execute &quot;INSERT INTO returns SELECT * FROM &quot; & _
&quot;[Text;Database=&quot; & PathtoTextFile & &quot;;HDR=YES].[TextFileName.txt]&quot;

Of course replacing the variable 'PathtoTextFile' with the correct value and TextFileName.txt with the correct file name.

Note: If the text file isn't set up the same as the MDB table (like different field counts) then you will need to add the fields to get, and the fields to insert, into the SELECT statement:

cn.Execute &quot;INSERT INTO returns(field1, field2) SELECT field1, field2 FROM &quot; & _
&quot;[Text;Database=&quot; & PathtoTextFile & &quot;;HDR=YES].[TextFileName.txt]&quot;

If the fields in the text file have different names, then the Schema.ini needs to map this. MS Jet will then use the correct ones. If the delimiter is different than the default setting in the registry, then this also needs to be set in the Schema.ini.

 

[jbradley]

CCLINT: Thank you! This is exactly what I was looking to do. The text file and Schema.ini were designed to have a 1:1 corelation to the database specifically to avoid having to deal with individual fields. The speed is blinding compared to having to build records one by one and field by field.

Is this documented in detail anywhere? I'd like to study up on the Why of it as much as the How.

Thanks again...

 

[jbradley]

How would I go about doing the same thing using a database on a MS SQL server as the target? The line:

db.Execute "INSERT INTO Returns SELECT * FROM " & _
            "[Text;HDR=NO;FMT=Delimited;Database=" & App.Path & "].[WorkFile.txt]"

works great if the connection string points to an Access database. If I point it to a database on a SQL server then I get an error &quot;Invalid object name 'Text;HDR=NO;FMT=Delimited;Database=C:\.WorkFile.txt'&quot;. I know the connection string is working because the routine that checks to see if the file has already been imported uses the same connection and it works.

Any thoughts on this problem?

 

[bjd4jc]

One thought would be to create a linked server on SQL Server to the text file. Then use the OpenQuery function in a SQL stored procedure to do what you are wanting to do. Look in the online books for sql server on how to setup a linked server. I use this and it works great.

 

[CCLINT]

More Info: Do a search in the MS Kb on &quot;schema.ini&quot; for the text file.

SQL Server: I never tried a different way than the following because it works fine - but there may be a better way: Use an MDB with a table Linked to the SQL Db. Everything else remains the same.

 

[jbradley]

It turns out that the SQLOLEDB provider doesn't have the same text functionality as the Jet provider so there is no way to do what I want, the way I wanted to do it. That being said, I found a function that uses the Microsoft SQLDMO Object Library to use the SQL Bulkcopy function to do the same thing. The link below will take you right to it:

http://www.freevbcode.com/ShowCode.Asp?ID=1629

The only problem I had implementing it is that some of my column names turned out to be SQL keywords and even enclosing them in square brackets in the Schema.ini wouldn't get me around the error that caused, so I ended up renaming them and fixing everything that depended on those columns.