By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,294 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,294 IT Pros & Developers. It's quick & easy.

Late binding with ADO and adModeShareExclusive problem

P: n/a
Hi Guys,

I am having a problem with a late binding conversion with ADO. I have
the following sub which is designed to be passed an ADO connection
object and also the full path to the .mdb file, then if the file
actually exists attempt a connection to it.

Sub ConnectMDB(ByRef connector As Object, ByVal filename As String)
Set connector = Nothing
Set connector = CreateObject("ADODB.Connection")
If FileExists(filename) Then
'Set connector = New ADODB.Connection
With connector
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source = " & filename & ";"
.Mode = adModeShareExclusive
.Open
End With
End If
End Sub

There seems to be an issue with the adModeShareExclusive. When I try
and use the code I am receiving a 'Variable not defined' on the mode
setting (it is highlighted) and the execution stops on the first line
of the sub. It works fine when the reference exists, but not without
it seems.

Any ideas?

The Frog
Jun 27 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Greetings,

It looks like you already tried

Set connector = New ADODB.Connection

What are you trying to accomplish? ADO is mainly for
connecting/Interfacing to/between an mdb and sql server or an mdb and
Excel (or possibly a text file), or from Classic VB (VB6) to sql server,
or an mdb, or excel...

If you are trying to use ADO between mdb's you are better off using DAO
- the native data construct of the mdb.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Jun 27 '08 #2

P: n/a
If you have a ref to ADODB you don't need late binding.
If you haven't, how could Access/VB know what adModeShareExclusive
is(12)?

I`ve been using ADO connections for more than ten years and I have
never yet wanted an exclusive connection. Then again I`ve never used
it to connect to an MDB.

These lines are nuts:
Set connector = Nothing
Set connector = CreateObject("ADODB.Connection")
If FileExists(filename) Then
'Set connector = New ADODB.Connection
Why would you create the connection twice… (… = question mark ... so
my keyboard is screwed up! Tant PIS!)
If you`re going to set the connection to something why would it matter
if it`s nothing or not nothing…

The best code is always short, simple code despite the dream sequences
of MS programmers and MVPs. Clearly, they are paid by the line.

On Jun 25, 11:31*am, The Frog <Mr.Frog.to....@googlemail.comwrote:
Hi Guys,

I am having a problem with a late binding conversion with ADO. I have
the following sub which is designed to be passed an ADO connection
object and also the full path to the .mdb file, then if the file
actually exists attempt a connection to it.

Sub ConnectMDB(ByRef connector As Object, ByVal filename As String)
Set connector = Nothing
Set connector = CreateObject("ADODB.Connection")
If FileExists(filename) Then
* * 'Set connector = New ADODB.Connection
* * With connector
* * * * .Provider = "Microsoft.Jet.OLEDB.4.0"
* * * * .ConnectionString = "Data Source = " & filename & ";"
* * * * .Mode = adModeShareExclusive
* * * * .Open
* * End With
End If
End Sub

There seems to be an issue with the adModeShareExclusive. When I try
and use the code I am receiving a 'Variable not defined' on the mode
setting (it is highlighted) and the execution stops on the first line
of the sub. It works fine when the reference exists, but not without
it seems.

Any ideas?

The Frog
Jun 27 '08 #3

P: n/a
Hi Phil,

Thanks for the response. I would have liked to use DAO, however the
circumstances prevent me from doing so. Most of the machines I have to
work with are running Access97, and the database format is 2000/XP/
2003. The option to upgrade the DAO on these machines does not exist.
They are running windows XP, so ADO 2.8 is present by default.

The idea, as you can see, is to connect to an MDB file, regardless of
97 format or newer (except for the newer still ACCDB - dont need to
worry about that at the moment). I need to set this all to late
binding and eliminate dependencies throughout the app. I havent come
across this issue before, and for the operations that will take place
I must ensure that the connection to the DB is exclusive.

I will keep working on this, and if you can think of anything that
would help then please let me know.

Cheers

The Frog
Jun 27 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.