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

 

[sanders720]

Apparent Issues with Connection to the RecordSet. See *** Line in code. Seems that the MsgBox and Debug.Pront commands just don't go through. Any thoughts, and Thanks again 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 COMP.CAT, COMP.DESC1, COMP.DESC2, COMP.MFG, COMP.LOC FROM [COMP] WHERE (((COMP.CAT) Is Not Null) AND ((COMP.LOC) = '&quot; & Me.cboSubAssy.Value & &quot;'));&quot;
Debug.Print sql

*** Set rs = New ADODB.Connection
rs.Open sql, cn, adOpenStatic, adLockReadOnly

rs.MoveLast
R = rs.RecordCount
rs.MoveFirst
For C = 1 To R
Debug.Print rs.Fields(&quot;CAT&quot
rs.MoveNext
Next C

' While Not rs.EOF
' Debug.Print rs.Fields(&quot;CAT&quot
' rs.MoveNext
' Wend

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

MsgBox (&quot;Program Completed Successfully...&quot

End Sub

 

[cmmrfrds]

Are you doing a Control G to look at the debug output?

The logic has got to hit this statement even if there are no records.
Debug.Print &quot;eof = &quot;; .EOF
Paste in the debug that shows the sql string.

 

[sanders720]

SQL is being set and is printing properly. It's even been checked in the database. One issue might be that my database is Access 97, even though we use 2000. The software generating the information I'm trying to read works with 97.

Otherwise, I am not convinced that rs is even being properly set here. I will do some testing with this, but do you have any other idea's?

 

[yamafopa]

Thanks cmmrfrds and sorry for butting in on your post, sanders...

Chris

 

[sanders720]

Not a problem, glad my question could help you.

 

[cmmrfrds]

This connection string is for an Access 2000 datebase, which is Jet 4.0. It will not work with Access 97

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;

Access 97 is Jet 3.51

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

 

[cmmrfrds]

Use the UDL Wizard to build and verify your connection string.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the microsoft Access provider – this will be Jet 4.0 for Access 2000 or Jet 3.51 for Access 97.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

 

[sanders720]

The Debug.Print Statements inside the With rs and While Not loops do not work. Otherwise, we must almost be there! I am consistently having this problem with 3.51 and 4.0 connection strings, so this may not be related. BASICALLY, NOTHING ABOUT THE RECORDSET IS HAPPENING.

Keep in mind that the database is Access 97, but is running under Access 2000 because the software creating the database does it in 97. Does this make a difference? I did run through the UDL example, which confirmed the connection worked fine under 3.51.

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.3.51;Persist Security Info=False;Data Source=\\DEEPBLUE\EngineeringBOM\VIA-WD User Files\&quot; & cboJobNo.Value & &quot;.mdb&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

 

[cmmrfrds]

Get rid of the line below
'--Set rs = New ADODB.Connection

Can you comment out the with statement and just put these 2 lines in instead

rs.Open sql, cn, adOpenStatic, adLockReadOnly
Debug.Print &quot;eof = &quot;; rs.EOF

It has to hit the debug.print statement otherwise it is not going through this code - this is puzzling. Paste in the results of the debug.print statement.

 

[sanders720]

No change to report. The Debug.Print command is not working. Could it be the Access 97 file format thing?

By the way, thanks for your help on this. It must be close... Believe it or not, I'm not trying to see gow many replies I can get or anything like that!

 

[cmmrfrds]

Is the debug.print working anyplace???? Can you paste in the results of the following debug.print statement placed just before the recordset open.

Debug.Print &quot;cn = &quot;; cn

rs.Open sql, cn, adOpenStatic, adLockReadOnly
Debug.Print &quot;eof = &quot;; rs.EOF

 

[sanders720]

YES - Below are the results of debug.print for sql and cn

SELECT COMP.CAT, COMP.DESC1, COMP.DESC2, COMP.MFG, COMP.LOC FROM [COMP] WHERE (((COMP.CAT) Is Not Null) AND ((COMP.LOC) = 'C01'))

cn = Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;User ID=Admin;Data Source=\\DEEPBLUE\EngineeringBOM\VIA-WD User Files\36529.mdb;Mode=Share Deny None;Extended Properties=&quot;;COUNTRY=0;CP=1252;LANGID=0x0409&quot;;Jet OLEDB:System database=&quot;&quot;;Jet OLEDB:Registry Path=&quot;&quot;;Jet OLEDBatabase Password=&quot;&quot;;Jet OLEDB:Global Partial Bulk Ops=2

 

[cmmrfrds]

I noticed this missing before the rs.open

Set rs = New ADODB.RecordSet

rs.Open sql, cn, adOpenStatic, adLockReadOnly
Debug.Print &quot;eof = &quot;; rs.EOF

The dim of the ADODB.RecordSet just creates the Object but it does not create an instance to work with. It takes the New keyword to create the instance. You apparently tried to create a New instance but did it on the Connection instead of the recordset. There is already a New instance of the connection so that is okay. Although, I am surprised there was not an error on the open. Do you have error messages turned off.

 

[sanders720]

Adding this back in produced no changes, still having the same problem!

 

[cmmrfrds]

If you want you can zip up the app and send it to me and I will take a look.

cmmrfrds@mchsi.com