VB accessing MS Access Query

[lisheen]

Hi All

I am trying to use VB to access a query in a MS Access Database and pass in a variable to run this query storing the query results in a recordset in VB. I am not very sure this is possible.

In other words I am using vb to pass the "Eqnum" variable to an Access Query and want to store the results in a recordset in vb called "rs"

Here's what I have so far, I have tried countless ways of doing this and had no luck ( the biggest problem is I dont really know what I am doing) Any help would be greatfully appreciated

Sub reprintlabel(Eqnum As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
  
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Str = "\\DATACHECK.MDB"
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Str + ";Persist Security Info=False"
cn.CursorLocation = adUseClient

ssql = "SELECT Nra3.WONUM FROM DATACHECK.Nra3  ??????????????

rs1.Open ssql1, cn, adOpenStatic, adLockReadOnly

I dont think that even the connect part is right as when I put in a ordinary select query and try to run it I get a compile error option not available. The database is currently just on my c drive, do I need to specify this? Thanks again for your help.

[choudhuryshouvi]

modify your code as follows :-

Sub reprintlabel(Byval Eqnum As String)
Dim cn As [B]New[/B] ADODB.Connection
Dim rs As [B]New[/B] ADODB.Recordset
[B]Dim str as String[/B]
  
str="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=[B]c:\DATACHECK.MDB[/B];Persist Security Info=False"
[B]cn.ConnectionString=str
cn.Open[/B]

[B]rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic[/B]

str = [B]"SELECT <YOUR FIELD NAMES> FROM <YOUR TABLE NAME> WHERE <REFERENCE COLUMN>='" & trim(Eqnum) & "'"[/B]

rs.Open Str, [B]cn, adOpenDynamic, adLockOptimistic[/B]
.
.
.
End Sub

hope this helps. compare the BOLDED parts with your code which you were originally posted here. if you still got any problem then post it right here.

regards
Shouvik

[lisheen]

Thanks very much for helping me it was really starting to drive me mad,

I put in the code below, but it returns an error "no value given for one or more required parameters" I added a message box just to ensure that the variable "Eqnum" was passed in okay and it seems fine, the debug highlights the "rs.open ... " line of code. Below is the code I put in. Can you by any chance see what I have done wrong.

Sub reprintlabel(ByVal Eqnum As String)

MsgBox Eqnum
    
   For j = 1 To 3000
    Let PNUM(1, j) = ""
  Next j
 
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim str As String
  
str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\DATACHECK.MDB;Persist Security Info=False"
cn.ConnectionString = str
cn.Open

rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

str = "SELECT WONUM FROM Nora3 WHERE EQNUM ='" & Eqnum & "'"

rs.Open str, cn, adOpenDynamic, adLockOptimistic

 If rs.EOF Then
    GoTo skpsub
  End If
      
  Let ctr = 1
  
  Do While Not rs.EOF
    Let PNUM(1, ctr) = rs!WONUM
     rs2.MoveNext
    Let ctr = ctr + 1
  Loop

      
skpsub:
'  Let PNUM(1, 0) = CStr(ctr)
  rs.Close
  'rs2.Close
  cn.Close

 End Sub

Thanks again for your help.

[lisheen]

Many Many thanks for all your help, I finally got it to work as it turned out the code was fine but in the access query I was selecting from I had the following expression "Like "*CAL*" " As it turned out even though Access was happy with that in the query, VB didnt like it as when I change this to "%CAL%" it worked fine.

Thanks again.