Setting The ADO Data Control ConnectionString Property At Runtime

[jerjim]

I have at lease one ADO data control on several VB forms.
Their ConnectionString property is set to the ff. value:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\

http://wwwroot\HR\Db\H3.mdb;Persist

Security Info=False

Please note that the database H3.mdb is on drive C.

For various reasons when I install my program on another computer,
the database has to be located on drive D instead of Drive C

Thus in the above connection string I have to replace C: with D:
and set at runtime the ConnectionString property of all my ADO Data Controls on all forms

With adoDataControl
.ConnectionString = sNewConnectionString
.Refresh
end with

The variable sNewConnectionString has the value:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Inetpub\

http://wwwroot\HR\Db\H3.mdb;Persist

Security Info=False

All that has changed is the drive letter.

The problem now is when the Refresh method is called,
VB flashes an error saying it could not find the file on Drive C:
But when I checked the value of the ConnectionString property,
it already has the value of the variable sNewConnectionString.

Is there anything else I should do?

 

[Terpsfan]

Is this a database that other users need to access? If so you need to put it on a network drive and then point to that in your connection string. Everyone should be mapped to the same network drive letter or the connection won't work. Also I'm not sure why you are putting a database in the inetserv folder. That's usually reserved for web pages you are going to put on your web server.

 

[jerjim]

First, this database is going to be accessed both from a VB.exe and from a ASP page running in a web app.

That's why i put it in a folder in inetpub.

My question is really simple.

Why does the refresh method of the ADO Data Control use the design time value of the ConnectionString property when just before I call the refresh method I HAVE ALREADY set the ConnectionString property to a new value?

 

[CarlosEscobar]

This is an interesting bug. I have no technical explanation as to why this bug occurs but when I got
it my solution was:

ChDir "D:\Inetpub\

http://wwwroot\HR\Db\H3.mdb"

before doing the connection.

Please let me know if this works for you.

 

[jerjim]

How do you execute a chdir command within a VB exe?

 

[thl]

Have you tried clearing the recordsource also? When there is a recordsource the ADOobject will try to connect before you can give it a new connectionstring.
Clearing the recordsource at designtime and setting the value at runtime after setting the new connectionstring prevents this.
I ran into the same problem and found my solution at:

http://ww2.cis.temple.edu/cis308/lectures/Unit3/Datagrid.htm

 

[Guest_imported]

with the DAO data control, there is a validate event procedure, what would be the similar procedure to use with the ADO data control?
also with the DAO control, I can set the RecordSetType to either table,snapshot or dynaset in the properties window, but the ADO control does not have that property in its properties window, so how do I go about to setting this property with an ADO control?