[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Directory problems with ADODC and Access DB

Posted on 2006-04-22
15
Medium Priority
?
889 Views
Last Modified: 2013-12-25
I've seen this problem discussed before, but still can't get it resolved in my app. I'm struggling with the app looking for the database in the wrong directory after using the CommonDialog control to open/save files.

I have an access database in the prog. directory and am trying to populate a datacombo with one of the fields. I've tried this two different ways:

(1) using an ADODC object on the form, setting the connectionstring and recordsource in the form_load event (designer is empty), then populating the datacombo:

Private Sub Form_Load()
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\accessdb.mdb;Persist Security Info=False"
Adodc1.RecordSource = "SELECT * FROM accessdb.fieldlist ORDER BY name"
Set DataCombo1.RowSource = Adodc1
DataCombo1.ListField = "name"
End Sub

(2) coding the connection and recordset directly in the form_load event:

Dim RS As Recordset, ADOCon As Connection, StrSql As String
Set RS = New Recordset
Set ADOCon = New Connection

ADOCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\accessdb.mdb;Persist Security Info=False"
RS.CursorLocation = adUseClient
StrSql = "SELECT * FROM accessdb.fieldlist ORDER BY name"

If ADOCon.State = adStateOpen Then
    RS.Open StrSql, ADOCon
    Set DataCombo1.RowSource = RS
    DataCombo1.ListField = "name"
Else
    ADOCon.Open
    RS.Open StrSql, ADOCon
    Set DataCombo1.RowSource = RS
    DataCombo1.ListField = "name"
End If
End Sub

Both methods successfully populate the datacombo when the associated form is loaded, provided that I don't load or save a file using the commondialog control beforehand. If I then unload the form, load a file, then reload the form, the app. looks for the database in the directory I was last in, even though I've used the App.Path variable. I have verified that the problem occurs with the statement "Set DataCombo1.RowSource = Adodc1" in the first approach, and with "RS.Open StrSql, ADOCon" with the second.

Anyone know of a solution??
0
Comment
Question by:de60
  • 6
  • 5
  • 3
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16515342
>>If I then unload the form, load a file, then reload the form, the app. looks for the database in the directory I was last in, even though I've used the App.Path variable.<<
Just verify that App.Path is in effect changed by using the Common Dialog box by doing something like the following prior to opening th database:
MsgBox App.Path

If that is the case, than you have no choice but to use a constant absolute path in your code or better still read it from an INI file.
0
 

Author Comment

by:de60
ID: 16515457
Yes, in fact I was checking the value of the connectionstring at runtime by using,

Msgbox (Adodc1.ConnectionString)

In all cases, App.Path does not change. If I watch the application directory I can verify that a read-only version of the desired Access database is created, so a connection is apparently made.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16515468
If you are saying that the path is the same in both cases, than I am afraid, I have no idea what is going on.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:de60
ID: 16515475
Quick update:

I changed the ConnectionString to include an absolute path to the database i.e.,

Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\path\accessdb.mdb;Persist Security Info=False"

and it still looks for the database in the latest Comm.Dialog directory.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 600 total points
ID: 16515835
First of all using data controls is a very bad idea.  The problem you are encoutering is the least of your future problems.  You should get in the habit of coding using ADO without using the ADO Data Control.  

Having said that, what is happeing I suspect (but I have not used the ADODC in over 8 years) is that you have not refreshed the control after assigning the ConnectionString property. As in:

ADODC1.Refresh
0
 

Author Comment

by:de60
ID: 16515985
Thanks for the suggestions.

At first I suspected the problem was with the ADODC, which led to the second method (code only). But the code I pasted above exhibits the same problem.

I tried refreshing the ADODC1 control, but no relief there.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16516617
I am afraid I have no idea.  

Good luck.
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 600 total points
ID: 16521996
>which led to the second method (code only).
>Dim RS As Recordset, ADOCon As Connection, StrSql As String

You are using ADO to connect, but have delared your object as DAO.  Try this:

Dim RS As ADODB.Recordset, ADOCon As ADODB.Connection, StrSql As String

Leon
0
 

Author Comment

by:de60
ID: 16529152
Leon,

I've have since been declaring in a Module, as an attempted work-around. So using your suggestion:

Public RS as ADODB.Recordset
Public ADOCon as ADODB.Connection

in my variables.bas

and within the startup form during the Load event:

Set ADOCon = New ADODB.Connection
ADOCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\accessdb.mdb;Persist Security Info=False"
StrSql = "SELECT * FROM accessdb.fieldlist ORDER BY name"
ADOCon.Open

Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient
RS.Open StrSql, ADOCon, adOpenDynamic, adLockOptimistic

End Sub

Then the other forms fill their datacombos and text fields with the data from RS.

I'm afraid to say that I still get path errors after using the CommonDialog to open or save a file. Any code that refreshes or refills the datacombos (i.e. any code that tries to connect to the database) triggers the error. For the time being, I have made the changes mentioned above, to establish and fill the RS variable with the data of interest on startup (before any load/save action). Most of my app simply uses that data, and doesn't require a refresh. The "editor" (adds, deletes, or edits database entries) has been removed and exists now as a separate app. This is of course just a work-around. I'd like to keep the editor integrated, and to refresh/requery all datacombos to reflect any modifications to the recordset.

Is there any way I could force the "last viewed" path (where I loaded the file) to the db path (App.Path)?
0
 

Author Comment

by:de60
ID: 16531490
As a last-hope effort, I've attached code from a test app that reproduces the problem. There's a dummy database in App.Path (accessdb), and I'm using two completely different datacombos (DC1, DC2), and associated connections (ADOCon, ADOCon2) and recordsets (RS, RS1). Upon startup, DC1 is filled with values from the database. DC2 is filled when a 'fillDC2' button is pressed. If a file is 'opened', the 'fillDC2' button will generate a path error. Does anyone see a problem with the following?

------------------------------
Private Sub Form_Load()
Dim RS As ADODB.Recordset, ADOCon As ADODB.Connection, StrSql As String

Set ADOCon = New ADODB.Connection
ADOCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\accessdb.mdb;Persist Security Info=False"
StrSql = "SELECT * FROM accessdb.table ORDER BY name"
ADOCon.Open

Set RS = New ADODB.Recordset
RS.CursorLocation = adUseClient
RS.Open StrSql, ADOCon, adOpenDynamic, adLockOptimistic

Set DC1.RowSource = RS
DC1.ListField = "name"
End Sub

---------------------------------------
Private Sub btn_open_Click()
Dim filelocation As String

CommonDialog1.ShowOpen

End Sub
----------------------------------------
Private Sub btn_fillDC2_Click()
Dim RS2 As ADODB.Recordset, ADOCon2 As ADODB.Connection, StrSql As String

MsgBox (App.Path) 'check to see if app.path was changed

Set ADOCon2 = New ADODB.Connection
ADOCon2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\accessdb2.mdb;Persist Security Info=False"
StrSql = "SELECT * FROM accessdb2.table ORDER BY name"
ADOCon2.Open

Set RS2 = New ADODB.Recordset
RS2.CursorLocation = adUseClient
RS2.Open StrSql, ADOCon2, adOpenDynamic, adLockOptimistic

Set DC2.RowSource = RS2
DC2.ListField = "name"

End Sub
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 16533793
>I'm afraid to say that I still get path errors after using the CommonDialog to open or save a file.

I do not see how this has anything to do with you connecting to the database.  What is the exact text of the error? Lets see your code around the CommonDialog control.  What kind of files are you opeing and saving?

Leon
0
 

Author Comment

by:de60
ID: 16536607
I managed to get it to work, but had to roll back to the 3.51 driver and Access 97 database. In addition, I had to restructure the way I was opening the connection and recordset:

Private WithEvents ADOCon As ADODB.Connection
Private WithEvents RS As ADODB.Recordset
----------------------------------
Private Sub Form_Load()
Dim strConnect As String, strProvider As String, strDataSource As String, _
strDataBaseName As String

strProvider = "Provider= Microsoft.Jet.OLEDB.3.51;"
strDataSource = App.Path
strDataBaseName = "\accessdb1.mdb;"
strDataSource = "Data Source=" & strDataSource & _
    strDataBaseName
strConnect = strProvider & strDataSource
   
Set ADOCon = New ADODB.Connection
ADOCon.CursorLocation = adUseClient
ADOCon.Open strConnect

Set RS = New ADODB.Recordset
RS.CursorType = adOpenStatic
RS.CursorLocation = adUseClient
RS.LockType = adLockPessimistic
RS.Source = "Select * From table"
RS.ActiveConnection = ADOCon
RS.Open

Set DataCombo1.RowSource = RS
DataCombo1.ListField = "name"
End Sub
------------------------------
Private Sub btn_requery_Click()
RS.Requery
DataCombo1.Refresh
End Sub
-------------------------------
Private Sub btn_open_Click()
CommonDialog1.ShowOpen
End Sub
-------------------------------

'this code is based off the ADO tutorial, http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=12050&lngWId=1


If I simply change the provider to Microsoft.Jet.OLEDB.4.0, point it to an Access 2000 database, and change the Sql command so as not to generate a 'FROM clause' error (RS.Source = "Select * From accessdb1.table"), then this code generates an error (Run-time error '-2147467259 (80004005)': Could not find file 'C:\last_viewed_directory\accessdb1.mdb')

I'm not sure if this is a bug, or simply an error in the way that I'm invoking the new driver, but I'm perfectly satisfied with using the older version. Though I came up with the solution, I certainly won't claim to have done it without your suggestions. I'll split the points between both of you, acperkins for cautioning against the use of ADODC, and leonstryker for elaborating on the ADO/DAO declarations.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 16536645
I am glad you have found  a solution for you problem, however awarding a "C" grade would by no means considered a reward.  I would much rather you asked for a refund, then get a that as a grade.

Leon
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16540133
Ah, yes.  How to win friends and influence people.  This will look very good in your grading history:
Last 10 Grades Given C

Good luck (with grades like that you may need it)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month18 days, 8 hours left to enroll

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question