DatabaseName equivilent in ADO Control
I have converted a project from DAO 3.6 Controls to ADO 2.8 by replacing the old DAO Data controls with ADO Data controls.
The DAO version connected to a password protected Access 97 database and the ADO version connects to a password protected Access 200 database. both databases have 4 tables which are related to to 4 controls on the form. Text box controls on the form are linked to the control fields
The ADO version runs OK but I am missing the ability to set the table source outside of the control.
using ADO i set the DatabaseName in the control to blank and then used the following code in the Form_Load
Dim MdbFile as Database
Dim IniFile as String
IniFile = App.Path + "\project.ini"
Call READINI ' ------reads database path and name from the INI file nto string PathToMdb
password = "aaaa" '----- password to access mdb file
Set MdbFile = DBEngine.Workspaces(0).Ope
dataexchange.DatabaseName = PathToMdb: dataexchange.Refresh
dataoverflt.DatabaseName = PathToMdb: dataoverflt.Refresh
DataTermnav.DatabaseName = PathToMdb: DataTermnav.Refresh
DataFIR.DatabaseName = PathToMdb: DataFIR.Refresh
in The ADO version I have used the ConnectionString property as:
Provider=Microsoft.JET.OLE
If I relace this connectionstring with
Provider=Microsoft.JET.OLE
I get an error
As the program is used in different environments where the name of the Access file and its location can vary, it is useful to use an external INI file to set the path so that it is not embedded in code.
Any Expert got the solution for me
Thanks
Rick
The DAO version connected to a password protected Access 97 database and the ADO version connects to a password protected Access 200 database. both databases have 4 tables which are related to to 4 controls on the form. Text box controls on the form are linked to the control fields
The ADO version runs OK but I am missing the ability to set the table source outside of the control.
using ADO i set the DatabaseName in the control to blank and then used the following code in the Form_Load
Dim MdbFile as Database
Dim IniFile as String
IniFile = App.Path + "\project.ini"
Call READINI ' ------reads database path and name from the INI file nto string PathToMdb
password = "aaaa" '----- password to access mdb file
Set MdbFile = DBEngine.Workspaces(0).Ope
dataexchange.DatabaseName = PathToMdb: dataexchange.Refresh
dataoverflt.DatabaseName = PathToMdb: dataoverflt.Refresh
DataTermnav.DatabaseName = PathToMdb: DataTermnav.Refresh
DataFIR.DatabaseName = PathToMdb: DataFIR.Refresh
in The ADO version I have used the ConnectionString property as:
Provider=Microsoft.JET.OLE
If I relace this connectionstring with
Provider=Microsoft.JET.OLE
I get an error
As the program is used in different environments where the name of the Access file and its location can vary, it is useful to use an external INI file to set the path so that it is not embedded in code.
Any Expert got the solution for me
Thanks
Rick
ASKER
I can create a connection string that works and will open the database using the path string.
However the connection string in Form_Load does not replace the connection string in the ADO Data Control.
in DAO you are able to set any valid DatabaseName, then select the datasource table name save the code and then create another string ie
Data1.DatabaseName = PathToMdb: Data1.Refresh
This string then replaces the string in the control.
If I try to relace the Connectstring in the ADO control with a code string I get an error.
However the connection string in Form_Load does not replace the connection string in the ADO Data Control.
in DAO you are able to set any valid DatabaseName, then select the datasource table name save the code and then create another string ie
Data1.DatabaseName = PathToMdb: Data1.Refresh
This string then replaces the string in the control.
If I try to relace the Connectstring in the ADO control with a code string I get an error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I agree that the adodc is not the best, howver the DAO data control was used origionally and the project uses 4 tables from the database which uses a total of 285 fields ( columns). The text box controls are easily bound to the adodc.
In code I would have to assingn each of the 285 table columns to a text box control for display.
I also find record matches with data1.recordset.find ...... which I can not find out how to use in code.
for the purpose that i need at the moment, the adodc control works well and does everything that I need except the ability to specify the source path outside of the control
I am going to try using adodc properties and see if that will work.
eg:
dim DataFIR as adodc
datafir.connectionstring = ".......etc
In code I would have to assingn each of the 285 table columns to a text box control for display.
I also find record matches with data1.recordset.find ...... which I can not find out how to use in code.
for the purpose that i need at the moment, the adodc control works well and does everything that I need except the ability to specify the source path outside of the control
I am going to try using adodc properties and see if that will work.
eg:
dim DataFIR as adodc
datafir.connectionstring = ".......etc
Try:
YourADOControl.ConnectionS
YourADOControl.Refresh
YourADOControl.ConnectionS
YourADOControl.Refresh
set adodc1.databasename = "Provider=Microsoft.Jet.OL
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Getting There!!
Leaving the ado data control connection string blank
and then Using a connection string in the form load
Dim PathToMDB As String
PathToMDB = "C:\try\NavRates.mdb"
Dim DBconnect As String
DBconnect = "Provider=Microsoft.JET.OL
DataFIR.ConnectionString = DBconnect
when the app is run an error pops up -
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
but when I click on OK the connection is found and the app run fine!.
It would seem that the problem lies with the ado data control being loaded and the bound controls being populated before the Forms On load or Open setting are read.
Any ideas of where to put the connection string so that it is read before the ado control is run ??
Leaving the ado data control connection string blank
and then Using a connection string in the form load
Dim PathToMDB As String
PathToMDB = "C:\try\NavRates.mdb"
Dim DBconnect As String
DBconnect = "Provider=Microsoft.JET.OL
DataFIR.ConnectionString = DBconnect
when the app is run an error pops up -
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
but when I click on OK the connection is found and the app run fine!.
It would seem that the problem lies with the ado data control being loaded and the bound controls being populated before the Forms On load or Open setting are read.
Any ideas of where to put the connection string so that it is read before the ado control is run ??
make the bounding when you press the ok button.
ASKER
Thats what happens automatically. Clicking the OK button on the error message allows the form to open and the connection string is read.
The data source folder is different on users machines, ( the PathTo Mdb string is read from an ini file) What I need to do is avoid the error message comming up especially as there are 4 ado data controls on the form
The data source folder is different on users machines, ( the PathTo Mdb string is read from an ini file) What I need to do is avoid the error message comming up especially as there are 4 ado data controls on the form
Did you try puting this on
Dim PathToMDB As String
PathToMDB = "C:\try\NavRates.mdb"
Dim DBconnect As String
DBconnect = "Provider=Microsoft.JET.OL
DataFIR.ConnectionString = DBconnect
the ok command button instead on the form load event?
Dim PathToMDB As String
PathToMDB = "C:\try\NavRates.mdb"
Dim DBconnect As String
DBconnect = "Provider=Microsoft.JET.OL
DataFIR.ConnectionString = DBconnect
the ok command button instead on the form load event?
ASKER
There is no way that the code can be put on the OK button that pops up when the form is opened. It just appears with the name of the adodc.It comes up before the form is loaded.
I have tried setting the PathtoMdb as a global variable and setting it in a startup form that then opens the main form - exactly the same message!!!
If I place "Provider=Microsoft.JET.OL
When the OK button on the message is clicked the form opens and displayes all the data correctly!!!!
I think that it is now time to give up on the MS ado data control. in the time spent trying to get it to work, I could have bound the 285 text box controls to the recordset in code!!
I have tried setting the PathtoMdb as a global variable and setting it in a startup form that then opens the main form - exactly the same message!!!
If I place "Provider=Microsoft.JET.OL
When the OK button on the message is clicked the form opens and displayes all the data correctly!!!!
I think that it is now time to give up on the MS ado data control. in the time spent trying to get it to work, I could have bound the 285 text box controls to the recordset in code!!
ASKER
I have increased the points and split them between JackofPH who has tried to find a solution and Leclairm who was right in advising that the ado control is more trouble than it is worth.
Thanks guy's
As quite often happens, Experts exchange sorts out what you can and can not do rather than find an exact fix!!.
It is just a pity that MS made some things easier in ADO compared to DAO but stripped out some useful features at the same time
Rick
Thanks guy's
As quite often happens, Experts exchange sorts out what you can and can not do rather than find an exact fix!!.
It is just a pity that MS made some things easier in ADO compared to DAO but stripped out some useful features at the same time
Rick
http://www.connectionstrings.com/
Try this
Dim mCN As New ADODB.Connection
mCN.CursorLocation = adUseClient
mCN.ConnectionString = "Provider=Microsoft.Jet.OL
mCN.Open