de60
asked on
Directory problems with ADODC and Access DB
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.OL
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.OL
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??
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.OL
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.OL
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??
ASKER
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.
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.
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.
ASKER
Quick update:
I changed the ConnectionString to include an absolute path to the database i.e.,
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OL
and it still looks for the database in the latest Comm.Dialog directory.
I changed the ConnectionString to include an absolute path to the database i.e.,
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OL
and it still looks for the database in the latest Comm.Dialog directory.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
I am afraid I have no idea.
Good luck.
Good luck.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.OL
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)?
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.OL
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)?
ASKER
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.OL
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.OL
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
--------------------------
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.OL
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.OL
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
>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
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
ASKER
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\
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.
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\
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.
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
Leon
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)
Last 10 Grades Given C
Good luck (with grades like that you may need it)
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.