Access SQL /VB6 connection help needed

[SKM]

Hi.
Im having a problem with an SQL statement.
Using VB6 and connecting to an Access 2000 database using an ADO Recordset i keep getting errors.
The database has 1 table named tbl_Master, from that table i need to select th following fields, ourref, yourref, date, insured2, totalcharges, insurancecompany and paymentreceived.
From these fields i need to return the data that matches 2 conditions,
a) the insurance company name in cboInsCo and
b) when paymentreceived = NO

Below is the SQL statement that i am using,


Private Sub cmdLst_Click()
Dim InsList As String
InsList = cboInsCo.Text

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\SKM_ICE Database\SKM_ICE DBase.mdb"
cn.Open
Set rsAccounts = New ADODB.Recordset
rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges"
From tbl_master
Where insurancecompany = InsList And PaymentReceived = "NO"

rsAccounts.Close
cn.Close
Set rsAccounts = Nothing
Set cn = Nothing

End Sub

[GaryMazzone]

is insuracncecompany stored as text in the DB? If so then the where statement would be: Where insuracnecompany = '" & text looking for here & "' And Paymentrecieved = "

again is Paymentrecieved a text field of boolean (Yes/NO) if Yes/No then Paymentrecieved = False (or Not Paymentrecieved)
if Paymentrecieved is text then PaymentRecieved = 'NO'"

[SKM]

Hi, sorry its took me so long to reply to your post, i havnt been able to get online for a while.

I tried the code you posted but got an error message while coding ,as shown below. sorry, no image as i cant work out how to paste it here, the option is not available.
Anyway i get a messagebox stating "Compile error: Expected: Expression" and ' is highlighted.
if i alter the code slightly to this

rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges"
From tbl_master
Where insurancecompany = " & InsList & " And PaymentReceived = "NO"

I get another message box error when i try to run the code stating "Compile error: sub or function not defined" and the word "WHERE" is highlighted.

any help would be appreciated.

[SKM]

Hello.
I urgently need help with the above SQL statement, my SELECT WHERE just returns errors.
Help Please.

SKM.

[szlamany]

That is not coded properly.

Should be:

rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges" _
   & " From tbl_master" _
   & " Where insurancecompany = " & InsList & " And PaymentReceived = 'NO'"

Note the line continuation characters - the proper "-quote use.

The spaces in front of the FROM and WHERE and the single-quotes around the NO.

[SKM]

Thanks szlamany,
I have tried your code and dont get any errors untill i run the code then i get the error
Runtime-error '3709'
Operation is not allowed on an object referencing
a closed or invalid connection.

When you debug the SELECT statement is highlighted.
The code i am using is shown below.

Dim cn As ADODB.Connection
Dim rsAccounts As ADODB.Recordset
 
Private Sub cmdLst_Click()
Dim InsList As String
InsList = cboInsCo.Text
 
Set cn = New ADODB.Connection
      cn.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\SKM_ICE Database\SKM_ICE DBase.mdb"
      cn.Open
Set rsAccounts = New ADODB.Recordset
  
Set cn = New ADODB.Connection
      cn.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\SKM_ICE Database\SKM_ICE DBase.mdb"
      cn.Open
  Set rsAccounts = New ADODB.Recordset
  
 rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges" _
  & " From tbl_master" _
  & " Where insurancecompany = " & InsList & " And PaymentReceived = 'NO'"
 
rsAccounts.Close
    cn.Close
    Set rsAccounts = Nothing
    Set cn = Nothing
 
End Sub

I use the same connection and recordset for other statements and have no problems so this error message is confusing me.

Hope you can help.

SKM
Thanks.

[szlamany]

Do you really have the connection code appearing twice in that routine?

[SKM]

Sorry, that was a pasting mistake,
the code is as below.

Dim cn As ADODB.Connection
Dim rsAccounts As ADODB.Recordset
 
Private Sub cmdLst_Click()
Dim InsList As String
InsList = cboInsCo.Text
 
Set cn = New ADODB.Connection
      cn.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\SKM_ICE Database\SKM_ICE DBase.mdb"
      cn.Open
Set rsAccounts = New ADODB.Recordset
 
 rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges" _
  & " From tbl_master" _
  & " Where insurancecompany = " & InsList & " And PaymentReceived = 'NO'"
 
rsAccounts.Close
    cn.Close
    Set rsAccounts = Nothing
    Set cn = Nothing
 
End Sub

One more question, How do you get your code in the forum displayed in a box as in your posts?

SKM.
Thanks

[szlamany]

Instead of [vbcode] tags I use [code] tags - I go back and forth...

The [code] tags will put scroll bars if the text is too large - sometimes that's good and sometimes I don't like it.

But back to your problem - that code looks pretty standard to me. I don't do ACCESS - so I cannot tell if the connection string is good or not.

Did you cut out code from your post that uses the recordset? I guessing you did.

Maybe change the select to this just for testing:

rsAccounts.Open "SELECT * from tbl_master"

[SKM]

Hi,
I changed the code from

rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges" _
& " From tbl_master" _
& " Where insurancecompany = " & InsList & " And PaymentReceived = 'NO'"

which at runtime returned the error

Run time error ‘3709’:
Operation is not allowed on an object referencing a closed or invalid connection.

When debugged the above SELECT statement was highlighted.

I changed the code as advised for testing to

rsAccounts.Open "SELECT * from tbl_master"

This returned the same Run time error 3709 and the SELECT statement was highlighted when debugged.

If I use the statement below I get no error but everything in the database is displayed in the ListView and not just the records I am after. The connection and recordset work ok until I use a SELECT statement.
Below is the full code I am using to connect to the Access database and display the returned fields in a Listview.

Option Explicit
Dim objCurrLi As ListItem
Dim InsList As String

Private Sub cmdLst_Click()
InsList = cboInsCo.Text
ListView1.ListItems.Clear   'Clear existing ListView
    
  Set cn = New ADODB.Connection
      cn.ConnectionString = "Provider =Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\SKM_ICE Database\SKM_ICE DBase.mdb"
      cn.Open
  Set rsAccounts = New ADODB.Recordset
  
     rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges" _
     & " From tbl_master" _
     & " Where insurancecompany = " & InsList & " And PaymentReceived = 'NO'"

Do While Not rsAccounts.EOF
     Set objCurrLi = ListView1.ListItems.Add(, , rsAccounts!ourRef & "") 'Our Ref
         objCurrLi.SubItems(1) = rsAccounts!YourRef & ""                 'Your Ref
         objCurrLi.SubItems(2) = rsAccounts!Date & ""                    'Date
         objCurrLi.SubItems(3) = rsAccounts!Insured2 & ""                'Insured2
         objCurrLi.SubItems(4) = rsAccounts!TotalCharges & ""            'Total charges
     rsAccounts.MoveNext
    Loop

    rsAccounts.Close
    cn.Close
    Set rsAccounts = Nothing
    Set cn = Nothing

End Sub

Any help would be much appreciated.

SKM.
Thanks

[Killazzz]

Hello,

If you open an adodb.recordset you need to specify the connection as well.

rs.open "select * from table1",cn

[SKM]

Hello, I changed the code to specify the connection by adding cn as shown below.

rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges", cn _
     & " From tbl_master" _
     & " Where insurancecompany = " & InsList & " And PaymentReceived = 'NO'"

Now when i run the code i get the following error message but im not sure what its trying to tell me, i think it means the SELECT statement is wrong

Run-Time error '-2147217805 (80040e73)':
Format of the initialization string does not conform to the OLE DB specification.


If anybody knows where i have gone wrong would you please spell it out to me.


SKM.
Thanks

[Killazzz]

Hello,

I've you've read my message correctly you would have seen that cn needs to be at the end of the open statement after your sql statement.

It's the second parameter.

so your code would be

rsAccounts.Open "SELECT OurRef, YourRef, Date, insured2, totalcharges" _
     & " From tbl_master" _
     & " Where insurancecompany = " & InsList & " And PaymentReceived = 'NO'" , cn

Jason

[SKM]

Thank you Jason for your reply.
Im fairly new to programming and SQL statements so please forgive any mistakes i make.
I havn't tried your last code yet and will post the results as soon as i do.
Thanks,

SKM

[Killazzz]

Did it work ?