Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

DatabaseName equivilent in ADO Control

Posted on 2006-03-20
13
Medium Priority
?
388 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



0
Comment
Question by:Aerocom
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 15

Expert Comment

by:Computron
ID: 16233925
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
0
 
LVL 1

Author Comment

by:Aerocom
ID: 16235733
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.

 
0
 
LVL 11

Assisted Solution

by:leclairm
leclairm earned 750 total points
ID: 16235942
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:Aerocom
ID: 16236306
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

0
 
LVL 11

Expert Comment

by:leclairm
ID: 16236406
Try:

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

Expert Comment

by:JackOfPH
ID: 16244670
set adodc1.databasename = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyDBLocationAndFileName
0
 
LVL 15

Accepted Solution

by:
JackOfPH earned 750 total points
ID: 16244694
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

0
 
LVL 1

Author Comment

by:Aerocom
ID: 16245942
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 ??
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 16253107
make the bounding when you press the ok button.
0
 
LVL 1

Author Comment

by:Aerocom
ID: 16254513
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
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 16264555
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?
0
 
LVL 1

Author Comment

by:Aerocom
ID: 16266295
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!!
0
 
LVL 1

Author Comment

by:Aerocom
ID: 16266334
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
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

810 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