VB Forum / Database Access / July 2008

I have a simple VB6 program which is working fine. I have a form with an
ADODC control on it I call "AdoCalendar". One of the properties is the
"Connectionstring". Amoung the items in the string is the location of my
access .MDB file.

The Connectionstring is as follows: Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\metis\Data TransferCSS\Ems Clinical Data\Schedules.mdb;Persist
Security Info=False

As I said, this is working fine. What I need to be able to do is set the
database location in the code instead of the properties for the control. When
the program is installed the path to the data will will be assigned and
stored in a reg setting. It will not be know at design time.

I have tried the following code but it does not work. It tells me that it
cannot find the path to the database etc.

I blank the "Connectionstring" property and put the following line in the
beginning of the Form Load sub routine.

Dim DataDir

Dim DataConStrg

DataDir = "\\metis\Data TransferCSS\Ems Clinical Data\"

DataConStrg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataDir &
"Schedules.mdb;Persist Security Info=False"

AdoCalendar.Connectionstring = DataConStrng

'other code...

'sample line to find record...

AdoCalendar.Recordset.MoveFirst

FindString = "calName=" & "'" & LabCalTitle.Caption & "'"

AdoCalendar.Recordset.Find FindString

The Error I get when the form loads is......

AdoCalendar

[Microsoft][ODBC Driver Manager]Data Source name not found and no default
driver specified

I then click "OK" and then get the following error, which after the
previouse error I would expect....

Microsoft Visual Basic

Run-time error '91':

Object variable or With block variable not set

I click "Debug" and it takes me to the first use of the recordset...

AdoCalendar.Recordset.MoveFirst

So bottom line... I get NO errors when I program the connectionstring via
the ADODC control properties box, but DO get errors if I try to set it via
code at form load.

As I said the code works fine except for that one issue.

So, does anyone know a way, using minimal changes to existing code, for me
to be able to use code to control the database location etc?

Thanks in advance,

Ralph Malph