Problems updating recordset with ADO

Dev Shed Forums Sponsor:

Get inside! Sample the range of functionality easily built with JMSL Library for Time Series Data Analysis, Heat Maps, Portfolio Optimization, Monte Carlo Simulation, Stock Price Charting and more. Download Now!

May 3rd, 2008, 06:34 AM

awyeah

Registered User

Join Date: Apr 2008

Posts: 18

Time spent in forums: 1 h 54 m 6 sec
Reputation Power: 0

Problems updating recordset with ADO

Currently I have a database (data.mdb) with 24 tables, and different table names. I am using
sql query to do some preprocessing on it. I create another (svmdata.mdb) file with 1 single table and
empty field (column names).

I wish to preprocess the data using sql query and add each of the results of the sql table
into a column in my new database file. Currently I am having some problems with the recordset
adding, it is not adding to the different columns in the new file, it adds all data into a single
column rather than the 24 columns I created.

Here is the code I use for creating my new db file with a table and 24 empty columns in it:

Code:

'''''''''''''''Create 25 column access database for svm training
Dim fso3
Dim svmdbpath       As String
Dim catNewDB3       As New ADOX.Catalog
Dim cn              As New ADODB.Connection
Dim strSQL          As String
Set catNewDB3 = New ADOX.Catalog
    
svmdbpath = App.Path & "\svmdata.mdb"
Set fso3 = CreateObject("Scripting.FileSystemObject")

If fso3.FileExists(svmdbpath) Then
   fso3.DeleteFile svmdbpath
End If

catNewDB3.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source=" & svmdbpath & ";"
Set catNewDB3 = Nothing

'Open db file
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & svmdbpath & ";"

'Create a new table for svm data (25 features and 1 label)
strSQL = "CREATE TABLE Data(F1 Single, F2 Single, F3 Single, F4 Single, " _
 & "F5 Single, F6 Single, F7 Single, F8 Single, F9 Single, " _
 & "F10 Single, F11 Single, F12 Single, F13 Single, F14 Single, " _
 & "F15 Single, F16 Single, F17 Single, F18 Single, F19 Single, " _
 & "F20 Single, F21 Single, F22 Single, F23 Single, F24 Single)"
cn.Execute (strSQL)

Here is the code I am using to add the records to my new file:

Code:

'Open newdata.mdb and subtract monthly consumption values
Dim cat             As New ADOX.Catalog
Dim rs              As New ADODB.Recordset
Dim rsNew           As New ADODB.Recordset
Set cn = New ADODB.Connection

Dim newdbpath       As String
Dim sql             As String
Dim month(0 To 2)   As String
Dim lngPosition     As Long

Dim featcols        As Variant
Dim subcols         As Variant
featcols = Array("F1", "F2", "F3", "F4", "F5", "F6", "F7", "F8", "F9", "F10", "F11", "F12", "F13", "F14", "F15", "F16", "F17", "F18", "F19", "F20", "F21", "F22", "F23", "F24")
subcols = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "010", "011", "012", "013", "014", "015", "016", "017", "018", "019", "020", "021", "022", "023", "024", "025")

newdbpath = App.Path & "\newdata.mdb"

'Open Customer ID file
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & newdbpath & ";"

'INNER JOIN records
For lngPosition = LBound(month) To UBound(month)

sql = "SELECT ABS([" & subcols(lngPosition + 1) & "].Consumption - " _
 & "[" & subcols(lngPosition) & "].Consumption) From " _
 & "[" & subcols(lngPosition + 1) & "], [" & subcols(lngPosition) & "]"
MsgBox (sql)
Set rs = cn.Execute(sql)
   
'Opening Catalog Connection
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & svmdbpath & ";"

'Open table
rsNew.Open "Select " & featcols(lngPosition) & " from Data", cat.ActiveConnection, adOpenKeyset, adLockOptimistic

'Initialising Counter
Do Until rs.EOF

With rsNew
  For Each fld In rs.Fields
  .AddNew
  .Fields(lngPosition).Value = fld
   Next
   .Update
End With

rs.MoveNext
Loop
rsNew.Close
Next lngPosition

Set rs = Nothing
cn.Close

I am having problems for adding my recordset into different columns.
Everytime I do a sql query I wish to add that to a different column of my new recordset (svmdata file).
Currently it add all data into the first column 'F1'.

It should be like, every time I query, that query should be added to 'F1' and then for the second
query add to 'F2' and so on, untill the 24 columns. All help is appreciated. I do not think this is
difficult, just am not able to grasp the method of adding and updating the records.

May 3rd, 2008, 07:30 PM

Doug G

Grumpier Old Moderator

Dev Shed God 12th Plane (10500 - 10999 posts)

Join Date: Jun 2003

Posts: 10,713

Time spent in forums: 4 Weeks 1 Day 23 h 57 m 59 sec
Reputation Power: 688

Review the recordset documentation in the msdn library. When you create a recordset from the execute method of your connection, you get a read-only forward-only cursor. You probably need to create your recordset object, then give it the desired locking and cursor properties, then open the query from the recorset object.

__________________
======
Doug G
======
"Hide, hide witch! The good folk come to burn thee. Their keen enjoyment hid behind their gothic mask of duty." -Mark Clifton

May 4th, 2008, 04:05 AM

awyeah

Registered User

Join Date: Apr 2008

Posts: 18

Time spent in forums: 1 h 54 m 6 sec
Reputation Power: 0

Oh okies thanks.. will look into the msdn as you suggested..

May 4th, 2008, 10:34 PM

awyeah

Registered User

Join Date: Apr 2008

Posts: 18

Time spent in forums: 1 h 54 m 6 sec
Reputation Power: 0

Currently I modified the code, it runs.. and gives me my desried output.. but if I remove '.AddNew' only in the Else statement (If lngPosition is Not 0).. But in the middle of execution of column "F2", I get this error and the program hangs up..

Run-time error '-2147217887 (80040e21)'
File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

Anyway to work around this? Since even I increase the locks in the registry file, this program needs to be automated, cannot increase registry entries of other PCs where it is run on also.

Code:

'Open table
rsNew.Open "Select " & featcols(lngPosition) & " from Data", cat.ActiveConnection, adOpenKeyset, adLockOptimistic

'Initialising Counter
rs.MoveFirst
If lngPosition = 0 Then
    Do Until rs.EOF
  
    With rsNew
     .AddNew
                 
    For fld = 0 To rs.Fields.Count - 1
     .Fields(fld).Value = rs.Fields(fld).Value
    Next
    .Update
    End With
    
    rs.MoveNext
    Loop
Else
    Do Until rs.EOF
    With rsNew
    
    For fld = 0 To rs.Fields.Count - 1
     .Fields(fld).Value = rs.Fields(fld).Value
    Next
    .Update
    End With
    
    rs.MoveNext
    rsNew.MoveNext
    Loop
End If
    rsNew.MoveFirst
    
rsNew.Close
rs.Close
Next lngPosition

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: