Late binding with ADO and adModeShareExclusive problem

[The Frog]

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

[Rich P]

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 ***

[lyle fairfield]

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 <[e-mail]:

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

[The Frog]

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