We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

DatabaseName equivilent in ADO Control

Aerocom
Aerocom asked
on
Medium Priority
414 Views
Last Modified: 2013-12-25
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



Comment
Watch Question

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

Author

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.

 
leclairm.Net Developer  ERP integration
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

leclairm.Net Developer  ERP integration

Commented:
Try:

YourADOControl.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & yourMDBLocation & ";Persist Security Info=False"
YourADOControl.Refresh

Commented:
set adodc1.databasename = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDBLocationAndFileName
Commented:
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

Author

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 ??

Commented:
make the bounding when you press the ok button.

Author

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

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

Author

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!!

Author

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
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.