What if I don't want to use Currentdb? 1

[sanders720]

Because my data is in a different access database? Is this possible?



Private Sub cmdGetData_Click()

Dim db As Database
Dim db_file As String
Dim sql As String
Dim rs As ADODB.Recordset

MsgBox Me.cboJobNo.Value

db_file = "v:\" & Me.cboJobNo.Value & ".mdb"

MsgBox db_file

Set db = OpenDatabase(db_file)

sql = &quot;SELECT CAT, DESC1, DESC2, MFG, LOC FROM tblComp WHERE CAT <> isnull and LOC = &quot; & Me.cboSubAssy.Value
Debug.Print sql

Set rs = db.OpenRecordset(sql)


Do While Not rs.EOF

' Syntax

Loop

rs.Close
Set rs = Nothing

 

[cmmrfrds]

This example is mixing ADO and DAO data objects so it won't work. You can access another database quite easily, best to get your other post working first.

 

[sanders720]

Okay, here's where I'm at. Eberything is working, though I cannot seem to get the c value for the recordcount. Can you see what I'm doing wrong?

Thnkyou in advance for the help!!!


Private Sub cmdGetData_Click()

Dim db As Database
Dim db_file As String
Dim sql As String
Dim rs As ADODB.Recordset
Dim c As Integer

If Me.cboJobNo.Value = &quot;&quot; Or IsNull(Me.cboJobNo) Then
MsgBox &quot;No Job No. was Selected!&quot;
cboJobNo.SetFocus
GoTo ER
ElseIf Me.cboSubAssy.Value = &quot;&quot; Or IsNull(Me.cboJobNo) Then
MsgBox &quot;No Sub Assy. was Selected!&quot;
cboSubAssy.SetFocus
GoTo ER
End If


MsgBox Me.cboJobNo.Value

db_file = &quot;v:\&quot; & Me.cboJobNo.Value & &quot;.mdb&quot;

MsgBox db_file

Set db = OpenDatabase(db_file)

sql = &quot;SELECT CAT, DESC1, DESC2, MFG, LOC FROM tblComp WHERE LOC = &quot; & Me.cboSubAssy.Value
MsgBox sql

Set rs = db.OpenRecordset(sql)
rs.MoveLast
c = rs.RecordCount
MsgBox c


' Debug.Print rs.Fields(&quot;CAT&quot ' , &quot;DESC1&quot;, &quot;DESC2&quot;, &quot;MFG&quot;, &quot;LOC&quot


Do While Not rs.EOF

' Syntax

Loop

rs.Close
Set rs = Nothing

ER:






End Sub

 

[cmmrfrds]

This is DAO syntax.
Set rs = db.OpenRecordset(sql)

The recordset rs is defined as ADO. What one do you want to use ADO or DAO - cannot mix.

If you want I can give you an example of using an ADO recordset and you can substitute your names.

 

[sanders720]

ADO would be great. I guess I see examples and get a little mixed up. I believe the application uses both in different instances.

 

[cmmrfrds]

EXAMPLE using the current database.
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset
Set cn = CurrentProject.Connection
'--- Get recordset from the database
sql1 = &quot; Select * IDTable &quot;
Set rs.ActiveConnection = CurrentProject.Connection
rs.Open sql1, cn, adOpenKeyset, adLockReadOnly

Debug.Print &quot;EOF = &quot;; rs.EOF

EXAMPLE reading an Excel spreadsheet - similiar to another Access database just different connection string.

Dim cn As New Connection
Dim rs As New Recordset
Dim connString As String
Dim sql1 As String, sql2 As String
connString = &quot;provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=C:\ATestDir\myTest.xls;&quot; & _
&quot;Extended Properties=&quot;&quot;Excel 8.0;HDR=Yes;&quot;&quot;;&quot;
cn.ConnectionString = connString
cn.Open connString

Set rs.ActiveConnection = cn
'-- Excel sheet name = newcustomers
sql1 = &quot;select * from newcustomers&quot;
rs.Open sql1, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print &quot;field value = &quot;; rs.Fields(0).Value
End If

 

[sanders720]

Your Excel example uses &quot;Extended Properties&quot; in the connString variable. I have no clue how to determine what this should look like. I know how to get the connection string in VB6, but how is it done in Access?

Thanks Again...


Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connString As String
Dim sql1 As String, sql2 As String
connString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source = v:\&quot; & cboJobNo.Value & &quot;.mdb&quot;
cn.ConnectionString = connString
cn.Open connString
Set rs.ActiveConnection = cn
sql1 = &quot;SELECT CAT, DESC1, DESC2, MFG, LOC FROM tblComp WHERE LOC = &quot; & Me.cboSubAssy.Value
rs.Open sql1, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
MsgBox (&quot;field value = &quot; & rs.Fields(0).Value)
End If

 

[cmmrfrds]

Here is an example of an Access connection string on another PC. You don't need the extended property. The vb6 connection string should work fine.

Dim rs As Recordset, connString As String
connString = &quot;provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\bigtuna\Databases\MotorRepairDB.mdb;&quot; & _
&quot;Persist Security Info=False&quot;

 

[sanders720]

I'm sorry, but something just isn't working here, otherwise my MsgBox would come up...


Private Sub cmdGetData_Click()

Dim cn As Connection
Dim rs As Recordset
Dim connString As String
Dim sql As String

connString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\deepblue\EngineeringBOM\VIA-WD User Files\&quot; & cboJobNo.Value & &quot;.mdb;&quot; & _
&quot;Persist Security Info=False&quot;

MsgBox connString

cn.ConnectionString = connString
cn.Open connString

Set rs.ActiveConnection = cn

sql = &quot;SELECT CAT, DESC1, DESC2, MFG, LOC FROM tblComp WHERE LOC = &quot; & Me.cboSubAssy.Value
Debug.Print sql

rs.Open sql, cn, adOpenForwardOnly, adLockReadOnly

MsgBox (&quot;field value = &quot; & rs.Fields(0).Value)



End Sub

 

[cmmrfrds]

Are you getting an error? Where? Does the debug.print sql produce the expected output?

Does the first msgbox show up?

These objects need to be instantiated by the New keyword.
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

 

[sanders720]

All of this seems to work, thus far! Thanks for the help.

 

[sanders720]

Is this now the right syntax to get the data out of the database? My Debug.Print is not working...

Thanks Again!

With rs
.Open sql, cn, adOpenForwardOnly, adLockReadOnly
.MoveLast
R = .RecordCount
.MoveFirst

For C = 1 To R
Debug.Print .Fields(&quot;CAT&quot;, &quot;DESC1&quot;, &quot;DESC2&quot;, &quot;MFG&quot;, &quot;LOC&quot
.MoveNext

Next C

 

[cmmrfrds]

You don't need this with ADO, it does not have the bug that was in DAO.
.MoveLast
R = .RecordCount
.MoveFirst


DO this for a test and we can go from here.
Debug.print &quot;eof = &quot; rs.EOF
While Not rs.EOF
Debug.Print &quot;field name = &quot;; rs.Fields(0).Name
Debug.Print &quot;field value = &quot;; rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
Set cn = Nothing

Once this is working there are some other things we can discuss on how to handle. Like, do you need to update etc..

 

[yamafopa]

First off let me say how timely finding this posting has been. I am just starting to migrate my DB over to ADO from DAO and finding some Access-specific examples is proving very helpful!

Second, may I ask cmmrfrds a question? IN the following bit of code...

Set rs.ActiveConnection = CurrentProject.Connection
rs.Open sql1, cn, adOpenKeyset, adLockReadOnly

... I wonder why you set a value for the ActiveConnection property of the recordset? I seems that you pass the ActiveConnection property of the Connection object (cn) in the next line. Doesn't that make the first line redundant?

Thanks in advance,
Chris

 

[cmmrfrds]

yamafopa, you are correct it is redundant. I was cutting and pasting out of a couple of different code pieces, but the example will still work okay for now.

 

[yamafopa]

cmmrfrds
Would you mind one more question?
When I run the following bit of code I get a value of -1 returned by rst.RecordCount. I know there are values in the Recordset as I can loop thru and print anticipated values to the debug window. Property reference says to expect an integer value representing the row count of the recordset in the RecordCount property, which is what you would expect (and get in DAO). However I seem to get a Boolean return...

-----------------------------------------------------------
Private Sub Command39_Click()
On Error GoTo EH

Dim rst As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim strSql As String

Set cn = CurrentProject.Connection
strSql = &quot;SELECT * FROM tblCustomer&quot;

rst.Open strSql, cn, adOpenDynamic, adLockBatchOptimistic

rst.MoveLast
rst.MoveFirst

Debug.Print rst.recordCount

SeeYa:
Set rst = Nothing
Set cn = Nothing
Exit Sub

EH:
MsgBox Err.Number & &quot; - &quot; & Err.description
Resume SeeYa

End Sub

-----------------------------------------------------------

Thanks again,
Chris

 

[sanders720]

Where is cn supposed to be defined?


Private Sub cmdGetData_Click()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connString As String
Dim sql As String
Dim C As Integer
Dim R As Integer


connString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\deepblue\EngineeringBOM\VIA-WD User Files\&quot; & cboJobNo.Value & &quot;.mdb;&quot; & _
&quot;Persist Security Info=False&quot;

MsgBox connString

cn.ConnectionString = connString

sql = &quot;SELECT CAT, DESC1, DESC2, MFG, LOC FROM tblComp WHERE LOC = &quot; & Me.cboSubAssy.Value
Debug.Print sql

Set rs.ActiveConnection = cn

With rs
.Open sql, cn, adOpenForwardOnly, adLockReadOnly
Debug.Print &quot;eof = &quot;; .EOF
While Not .EOF
Debug.Print &quot;field name = &quot;; rs.Fields(0).Name
Debug.Print &quot;field value = &quot;; rs.Fields(0).Value
rs.MoveNext
Wend
.Close
End With

Set cn = Nothing


End Sub

 

[cmmrfrds]

To answer your question yamafopa, the record count can be relied on is some cases but not others. It is usually dependent on the cursor. A static client side cursor will always provide a record count. A forward only cursor will never provide a record count and there are variations in between. It is a combination of cursor type and cursor location, but each provider may not implement. That is, Sql Server may be different than Access and so on.

The default cursor location is server side. In your example, if you change the location to client side it should give the record count. You may need to verify the different combinations. I usually retrieve small recordsets and keep them client side since I believe this is most efficient and flexible.

 

[cmmrfrds]

What you have should be okay.

Dim cn As New ADODB.Connection

It looks like I missed opening the connection.

cn.ConnectionString = connString
cn.Open '- open the connection after conn string is built

 

[sanders720]

The Debug.Prrint Statements inside the With rs and While Not loops do not work. Otherwise, we must almost be there!

Thanks for the help...


Private Sub cmdGetData_Click()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim connString As String
Dim sql As String
Dim C As Integer
Dim R As Integer


connString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; & _
&quot;Data Source=\\deepblue\EngineeringBOM\VIA-WD User Files\&quot; & cboJobNo.Value & &quot;.mdb;&quot; & _
&quot;Persist Security Info=False&quot;

MsgBox connString

Set cn = New ADODB.Connection
cn.Open connString

sql = &quot;SELECT * FROM tblComp WHERE LOC = &quot; & Me.cboSubAssy.Value
Debug.Print sql

Set rs = New ADODB.Connection
With rs
.Open sql, cn, adOpenStatic, adLockReadOnly
Debug.Print &quot;eof = &quot;; .EOF
While Not .EOF
Debug.Print &quot;field name = &quot;; .Fields(0).Name
Debug.Print &quot;field value = &quot;; .Fields(0).Value
.MoveNext
Wend
.Close
End With

Set cn = Nothing
Set rs = Nothing


End Sub