What does Error: Object Variable or With Block Variable Not Set

  • Thread starter Thread starter Dominique
  • Start date Start date
D

Dominique

Please bare with me as i try to explain the scenario.

Scenario 1
I have a Development environment, which is a server.
From my desk, I am mapped to a folder in the development
environment that has an MS Access database. In other
words, I am coding from my desk, but the files are located
in another server. Here's the problem: when I try to
connect to another MS access database from the current one
I get this error: Object Variable or With Block Variable
Not Set. I even try creating a VB apps within that folder,
thinking that I should be able to connect, but it didn't
work.

Scenario 2
I have two different MS access database file in the same
folder:
MS access A
MS access B

I want to create a connection from MS access A to MS
access B without specifying a Drive letter:
i.e
Oconn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=F:\WWCS\Fru\FRUDSLIM.mdb"

why can't I do this:
Oconn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\WWCS\Fru\FRUDSLIM.mdb"
 
Object Variable or With Block Variable Not Set
Means that you are trying to use an object without SETting it first

sub Dummy(0
dim db as database
dim rst as recordset

set rst =db.openRecordset("tbl")

end sub

will cause the error when it tries to open the recordset as the db
variable has not been set yet.
To fix the above you would need to add
Set db =CurrentDB
before the Set rst = ... line

In your case, you will need to go through your code looking for the
object variable that is not set, and set it appropriately

for your second scenario,
MSAccess insists on a fully qualified path to a connection
It doesn't have to be "C:\..."
It can also be a UNC or URL as well

eg \\MyServer\MyFolder\...

Using hard coded strings here (or anywhere) is NOT a good idea
When your datasource moves, and it will, you will need to recode your
connections

Try using a user selection for the filename, or at least the Drive
number
Your code then becomes
....
"DataSource=" & strMyDatabase

or (not so good)
"DataSource=" & strMyDrive & ":\Path\File.mdb"

You can get the user to select the file once using the Windows
OpenFIle boxes, then save the information to the registry for future
use.
 
Back
Top