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).OpenDatabase(PathToMdb, 0, 0, ";pwd=" & password)
    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.OLEDB.4.0;Data Source=c:\MyDirectory\MyTables.mdb;Jet OLEDB:Database Password=aaaa

If I relace this connectionstring with
Provider=Microsoft.JET.OLEDB.4.0;Data Source=PathToMdb;Jet OLEDB:Database Password=aaaa
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



LVL 1
AerocomAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ComputronCommented:
Nice reference here
http://www.connectionstrings.com/

Try this

    Dim mCN As New ADODB.Connection

    mCN.CursorLocation = adUseClient
    mCN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDBLocationAndFileName & ";UserId=admin;Password=;"
    mCN.Open
AerocomAuthor Commented:
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.

 
leclairmCommented:
I would advise you not to use a data control.  It causes more problems than it's worth.

Here are some links to help with using ado directly:

http://www.w3schools.com/ado/default.asp
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=12050&lngWId=1
http://www.functionx.com/vb/Lesson13.htm
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

AerocomAuthor Commented:
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

leclairmCommented:
Try:

YourADOControl.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & yourMDBLocation & ";Persist Security Info=False"
YourADOControl.Refresh
JackOfPHCommented:
set adodc1.databasename = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDBLocationAndFileName
JackOfPHCommented:
oopss.. wrong post hehehe!! sorry

this is the right one

try this to make a connection

dim Mydb
mydb=path of the database e.g: app.path & "\database.mdb"
adodc.connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & Mydb & "';Persist Security Info=False"

and this to open a recordset

adodc.recordsource="select * from Table"
adodc.refresh

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AerocomAuthor Commented:
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.OLEDB.4.0;Data Source= " & PathToMDB & " ;Jet OLEDB:Database Password=aaaa"

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 ??
JackOfPHCommented:
make the bounding when you press the ok button.
AerocomAuthor Commented:
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
JackOfPHCommented:
Did you try puting this on

Dim PathToMDB As String
PathToMDB = "C:\try\NavRates.mdb"
Dim DBconnect As String

DBconnect = "Provider=Microsoft.JET.OLEDB.4.0;Data Source= " & PathToMDB & " ;Jet OLEDB:Database Password=aaaa"

DataFIR.ConnectionString = DBconnect

the ok command button instead on the form load event?
AerocomAuthor Commented:
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.OLEDB.4.0;Data Source= " & PathToMDB & " ;Jet OLEDB:Database Password=aaaa" in the controls connection string property, the message changes to " Could not find file 'C:\adotest\& PathToMdb &'
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!!
AerocomAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.