VBA only finds one record

[OceanaPolynom]

Hello ridders52
So far after trying various thing, I have not been able to make this work. My last attempt looked like this:

projdirect$="C:\Temp\Today"
Set dbsObj = DBEngine.Workspaces(0).OpenDatabase(projdirect$ & "\temp.mdb")
Set rstObj = dbsObj.OpenRecordset("tblcoorsJHL2", dbOpenTable)

dbsObj.Execute "INSERT INTO tblclcircles ( yxdata, xxdata, zxdata )" & " SELECT " & y * 1000 & ", " & x * 1000 & ", " & z * 1000 & "," & " FROM " & FileName$ & ";", dbFailOnError

When the sql statement is executed I get this error:
Syntax error in INSERT INTO statment

At the time of the error the execute statement looks like this:


INSERT INTO tblclcircles ( yxdata, xxdata, zxdata ) SELECT 0, 0, 0, FROM C:\Temp\Today\Sample0.csv; 128

The mdb file is C:\Temp\Today\Temp.mdb
The columns yxdata xxdata zxdata are defined as Long Integers

The csv file is C:\Temp\Today|\Sample0.csv
A typical row in the csv file looks like this:
180837.71659,665403.27629,-14.71753
This is a co-ordinate on the national grid. I need millimeter preccision so I multiply them by 1000 before adding them to the db table.
Thank you
John

[isladogs]

Not quite what I meant

For future info, use a Debug.Print line so show the result of code in the Immediate window

Anyway starting again ....
You need to link or import your csv file - I suggest link it - and if necessary give it an alias Today
To make this as simple as possible, use the same file name and location for your source data each time you do this

Then use a query or a procedure to append the data

Query version

INSERT INTO tblclcircles ( xxData, yxdata, zxdata )SELECT 1000*[x] AS xxdata, 1000*[y] AS yxdata, 1000*[z] AS zxdata
FROM Today;

Procedure:

Sub AppendTodayData()

    CurrentDb.Execute "INSERT INTO tblclcircles ( xxData, yxdata, zxdata )" & _
        " SELECT 1000*[x] AS xxdata, 1000*[y] AS yxdata, 1000*[z] AS zxdata" & _
        " FROM Today;", dbFailOnError


End Sub

NOTE:
1. That's the entire code for the append.
NONE of the recordset code or Dim statements are needed
2. This code will not protect against repeated appending of the same data.
However it can easily be tweaked to prevent that.
Let me know if you need assistance with that

Attached zip file contains an MDB & ACCDB version of this together with a CSV file with 3 random records
The CSV file has been linked already
Use the linked table manager to relink it to the correct location on your PC

Run the code or query.
That's it

HTH

[OceanaPolynom]

Hello ridders52
This worked 8-9 times aster than what I was doing. It is not what I had in mind exactly. I don't open Access to do things, I run VBA code from inside AutoCAd to do database operations. Access does not have to be installed. I tried to get your code run like this but did not succeed. This is still a much faster way to load the data into the table.
Thanks again.
John

[isladogs]

Hello ridders52
This worked 8-9 times aster than what I was doing. It is not what I had in mind exactly. I don't open Access to do things, I run VBA code from inside AutoCAd to do database operations. Access does not have to be installed. I tried to get your code run like this but did not succeed. This is still a much faster way to load the data into the table.
Thanks again.
John

I'm confused. It was about 9 times faster (as I suspected) ... but you didn't succeed in getting it to run.
Can you adapt the principle to do the same in AutoCAD?
I can't test that for you as I don't have that program

If not, does the speed increase mean that doing the processing in Access itself is worthwhile?

In general, recordsets are slow due to looping through each record in sequence

[OceanaPolynom]

Hello
What I meant is that I got it to work but only by opening Access. It is still so much faster that it is well worthwhile to do it your way. Which is why I marked it as solved. Thank very much indeed.
John

[isladogs]

Excellent. Glad to have helped.