Data Environment & Access 2000

I upgraded my access 97 database to access 2000.  Unfortunately, my vb6 project was a victim of the switch. I upgraded my VB6 to SP3.  I successfully switched the data controls to adodc and have one problem left.  It pertains to the Date Environment.

I want to use the Microsoft OLE DB Provider for ODBC Drivers as a provider.  The ConnectionSource is DSN=MS Access Database;DBQ=c:\pccomp\residuals.mdb; DefaultDir=app.path;DriverId=25;  FIL=MS Access;MaxBufferSize=2048;PageTimeout=5 ;UID=admin;

When I press “Test Connection” it connects.  Unfortunately, I want my users to be able to install my application on a different drive other than “C” so I must be able to refer to the drive by use of the app.path & “[database.mdb].

My previous version (using Jet 3.51) allowed the ConnectionString of

"Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & App.Path & "\residuals.mdb" & ";Mode=Read;Jet OLEDB:Database Password=479764818"

It worked perfectly, now I need to do the same thing for the ConnectionSource instead of the ConnectionString.

1.Can I modify the ConnectionSource string in the Properties window?  In other words substitute something for the C:\PCComp directory with the app.path?
2.Can I assign the data environment at run time using source code?

This is my first post here so please help.  Microsoft has nothing online or in their help files at all (surprise).

Jim Hoeger
Who is Participating?
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.

JHoegerAuthor Commented:
Adjusted points to 100
Éric MoreauSenior .Net ConsultantCommented:
Sure you can but you must change your ConnectionString property and open your DataEnvironment before binding anything to it.

To do it, add a standard module to your project and set the startup object to "sub main".

Private Sub main()
    DataEnvironment1.Connection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"
End Sub
JHoegerAuthor Commented:
Thanks, but this does not allow for the default directory to be any directory that the user decided to load my program into.

I want "app.path" to be the directory not c:\pccomp or d:\pccomp etc.  I can't refer to a concrete directory in the program when that directory may not exist in the end users computer.

I have successfully done this on the daodc controls dynamically and also could do it under the jet 3.61 with the data environment by using:

str1DB = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=" & App.Path & "\residuals.mdb" & ";Mode=Read;Jet OLEDB:Database Password=479764818"

and then assigning the connectionsource to the str1DB variable.

Thanks for the help, I appreciate it.
If I am misunderstanding something, let me know.  I've only been programming in VB6 for a year.

Jim Hoeger
Éric MoreauSenior .Net ConsultantCommented:
Replace the hard-coded path with your App.Path property just as you did using DAO. You can also use the driver you want.

The real trick here is that you to open the data environment in the sub main.

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
JHoegerAuthor Commented:
Thanks, The biggest problem I had is including a " inside the string.  I did it by adding the CHR(34).

Now other problems have arisen, so I am going to use the Jet 4.0 engine if it's the last thing I do.

It works OK when there is no password, but errors out.

Thanks for the help, I'm going to post another (hopefully final) question pertaining to passwords with jet 4.0

Jim Hoeger
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.