Looking through 1 column of a recordset

[w11184]

Hi,

I have currently set my recordset to the following:

Set rs = db.OpenRecordset("SELECT RecordNo, CarModel FROM tblCarData WHERE CarModel Like 'Ford*'", dbOpenDynaset)

So that when I insert data into a new table I can output the RecordNo and CarModel but I only want to look through the CarModel column. At the moment my code looks like this

    Do While Not rs.EOF
    
        For Each fld In rs.Fields

strSql = "INSERT INTO tblCarDataResult (RecordNo,CarModel)" & _
            "VALUES ('" & rs!RecordNo & "','" & rs!CarModel  & "')"
            
            DoCmd.RunSQL strSql

  Next 'END FOR EACH
        rs.MoveNext
        
    Loop
    
    DoCmd.SetWarnings True

But this looks through all columns so my results are doubled.

 

[tehNellie]

Obvious question time....

Why not just use a query?

On a more practical note, you run the INSERT query twice because of the FOR loop which runs the query once for every column in your recordset.

 

[w11184]

Hi yeah I should have been clearer. I actually manage to get it working by putting:

If fld.Name = "CarModel" Then

before the query. But the reason I've done it using vba is because I need to build a frontend that will allow the user to pick tables and columns.

 

[tehNellie]

Still doesn't alter the fact that the best way to update/insert one table based on the contents of another is a Query. You can do the whole lot in one go, no looping, recordsets or other shenanigans required.

You don't need any check on the field(s) per row at all. You have a fixation with stepping through every column in every row, it's not needed, stop it

Your initial query also suggests that you need to look at CardDataResult table.

cardmodel should be split into CarMakerID linked to a carMaker Table (ie Ford) and CarModel (Escort), then you can query using WHERE carmaker = "Ford" rather than having to use a LIKE

 

[w11184]

haha, I guess I am still finding my way round vba and sql (and Access) at the moment so I know I am doing a things in a bit of an odd way at times.

The example I use was only an example so I know it's not the best way to store data but I didn't want to bore you with the details!

But if you do want to be bored...

Basically I need a script that will go through a data set and if it finds anything that that looks like this "err1 - 5 To 10". It will work out the middle value, in this case is 7 and updates the record.

Hence why I am doing it in vba...
Any ideas are welcomed though!

 

[tehNellie]

I'd still use a query. I'd go as far to opine that a Function that took your string "err1 - 5 to 10" and worked out the correct value might be appropriate, but some more context about what that string actually means, and what values it might actually be would be useful.

Recordsets are incredibly useful and powerful tools, but on a general basis if you want to do something to data in a table (based on information in another) a Query should be your first port of call.

Certainly the example that you originally posted doesn't need a recordset you can achieve what the code does entirely in a query and insert/update the data in one go rather than record at a time. In context, how you're using the recordset to drive an INSERT query can be a valid method, but in your example, it's taking something simple and making it far more complicated than it needs to be.

To do what your code is doing in a query:

INSERT INTO tblCarDataResult (RecordNo,CarModel)
SELECT recordNo, Carmodel
FROM tblCarData 
WHERE CarModel Like 'Ford*'"

One query and all your data is done in one go rather than one record at a time. You can make 'Ford*' a parameter that your code passes data to and drive the population of that parameter with a few lines of code if you want and the bulk of the grunt work that would be slow in code is being handled by the query.

my biggest table in Access is just short of two million rows of data, if I stepped through that one row at time I'd be ready to retire by time it finished.

But if you do want to be bored...

I want to give good advice, the more accurately you can describe what it is you're actually trying to do the better information people can give you.