VBE Runtime Error - Invalid SQL statement

[firefly2k8]

Hi,

I have created a database with a table. I am trying to access some records using the VBE and print them to the immediate window. Trying to do it properly so followed this code from a book. But it just gives me a runtime error. Any ideas?

Private Sub Command0_Click()
Dim rst1 As ADODB.Recordset

    'Create Recordset reference and set its properties
      Set rst1 = New ADODB.Recordset
      rst1.CursorType = adOpenKeyset
      rst1.LockType = adLockOptimistic
      
      'Open recordset, print test record
      
      rst1.Open "PID:6", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\example folder\MyProject.mdb;"
        
      Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value
      
      'Clean up objects
      rst1.Close
      Set rst1 = Nothing
  
End Sub

 

[the_net_2.0]

I believe it's because you have:

PID:6

in the sql statement argument, isn't it?

I think that's supposed to be an option, in the options argument section.

(laughing at this coming from a book!)

 

[firefly2k8]

PID:6 happens to be the name of my table.

So forget the book and the code here. I have a table sitting in an mdb file. I want to loop through each record, perform some calculations based on the contents, and enter the result into other records in the same table.

1) Should the code that I will use sit in a module as a sub procedure?
What is the appropriate way to:
2) connect to a database - is it necessary?
3) loop through all records
4) perform a calculation on a record, note the calcualtions will be too complex for an SQL statement that will apply to whole columns at once.
5) output the result of a calculation to the table

The task seems to me to be very simple and fundamental, but i can;t seem to find a solution. Not enjoying programming with access so far.

Help much appreciated.

 

[DCrake]

You should not be storing calculations in tables anyway. Why are you doing it? or should I say trying to do it.

 

[firefly2k8]

I am storing the result of a calculation.

Is it so strange to want to perform some calculations on data stored in an access table and output the results to that table.

 

[DCrake]

If you store calculations in a table based on information from the same table then you have to make sure that each and every time you make an amendment to any of the components that make up the calculation you would need to recalculate the value and update the calculated field.

Lets take an example

Persons Age

We know their date of birth and we what todays date is, so in a query we would calculate

AgeateDiff("YYYY",DOB,DATE)

Because tomorrow they may be a year older. So every day you would have to perform an update query on the table to ensure that the persons age was correct.

 

[firefly2k8]

My table is not dynamic in that sense.

So the user enters the data, runs the calcs, gets an output, the end.

I managed to get somewhere with a bit of trial and error. Feel free to pick this code apart and tell me of any poor practises:

Private Sub PrepMktDataBtn_Click()
  Dim cn As ADODB.Connection
  Dim rsProjectTable As ADODB.Recordset
  'Dim strSQL As String
  Dim output As Double
  
  'strSQL = "SELECT 'Project Table 1'.[Sell Amount] FROM 'Project Table 1';"
  
  Set rsProjectTable = New ADODB.Recordset
  
  rsProjectTable.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= c:\File Path\FileName.mdb;"
  
  'rsProjectTable.Open "SELECT [Project Table 1].* FROM [Project Table 1];", , adOpenKeyset, adLockOptimistic, adCmdTable
  rsProjectTable.Open "[Project Table 1]", , adOpenKeyset, adLockOptimistic, adCmdTable
  
With rsProjectTable
  Do While Not .EOF
    output = .Fields(6) / .Fields(7)
    .Fields("Base Amount") = output
    .Update
     Debug.Print .Fields("Base Amount")
     .MoveNext
  Loop
 End With
  rsProjectTable.Close
  Set rsProjectTable = Nothing
  
End Sub

 

[DCrake]

Your query would just need the following

BaseRate:[field6]/[field7]

BTW don't use field numbers in your code. Use their names. It may be that in the future you add/remove/reorder a field in the table and it may upset the numbering logic you have.

 

[firefly2k8]

Yes a query would do this operation easily. But eventually i want to do more complex calcs and logic that i don't think a query could handle.

For example next task is to work out the number of working days between two dates. As far as I know networkdays is not available so i will have to use a sub procedure to do this operation on each record.

 

[DCrake]

If you do a search there is a function already in place to calculate newworkdays. And this can be run from a query.