Asp Forum - How can I link external Access database in VB6 without requiring DAO360.dll?

(Mike Mitchell)

5/1/2012 1:25:00 PM

At present, to link a table from an external database, I do:

Private Sub LinkExternalData()

Dim db As Database
Dim table_def As TableDef

' Open the main database
Set db = OpenDatabase("C:\MyPath\cls_Idx.mdb")

' See if the link already exists.
On Error Resume Next
Set table_def = db.TableDefs("Data")
If Err.Number <> 0 Then
On Error GoTo 0
' The link does not yet exist. Create it.
Set table_def = New TableDef

' Create a TableDef defining for foreign database.
table_def.Connect = ";Database=" & "C:\MyPath\cls.mdb"

' Specify table name in foreign database.
table_def.SourceTableName = "Data"

' Give it the same name in the main database.
table_def.Name = "Data"

' Append table definition
db.TableDefs.Append table_def

MsgBox "Link created"
End If

On Error GoTo 0

' Close the database.

db.Close

End Sub

However, this requires a reference to DAO360.DLL. Can the same thing
be achieved using e.g. ADOX, and if so, how?

MM

12 Answers

GS

5/1/2012 2:43:00 PM

You can use ADODB with a ref set to its lib. (I'd use the earliest
version you have and let the OS update at runtime)

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ralph

5/1/2012 4:39:00 PM

On Tue, 01 May 2012 14:24:42 +0100, MM <kylix_is@yahoo.co.uk> wrote:

>At present, to link a table from an external database, I do:
>
/snipped code
>
>However, this requires a reference to DAO360.DLL. Can the same thing
>be achieved using e.g. ADOX, and if so, how?
>

This will give you a good idea on how to use ADOX.

"ADOX Programming Code Examples"
http://allenbrowne.com/func...

It is useful to note that ADOX was added as an extra after ADO was
released and developers complained on the lack of DDL (Data Definition
Language) in the ADO object model. It is obviously built over OLE DB,
as is ADODB, but keep in mind it is NOT part of the ADODB object
model, thus as noted in the above article can be construed as
"unstable".

This unstablility typically comes about when developers unknowningly
start to mix objects - thinking they are dealing with the same thing
when they are not. Besides the obvious issue of hammering on the same
data objects with both libraries at the same time, there is the more
subtle problems introduced when you mix both DDL Queries via ADODB,
and ADOX DDL procedures.

Best practice is to keep your "ADOX", or DDL, activities separate from
your "ADODB" activities, i.e. have two hats; be aware at all times
which hat you have on; and carefully swap them as needed. <g>

-ralph

GS

5/1/2012 5:03:00 PM

ralph explained on 5/1/2012 :
> On Tue, 01 May 2012 14:24:42 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>
>> At present, to link a table from an external database, I do:
>>
> /snipped code
>>
>> However, this requires a reference to DAO360.DLL. Can the same thing
>> be achieved using e.g. ADOX, and if so, how?
>>
>
> This will give you a good idea on how to use ADOX.
>
> "ADOX Programming Code Examples"
> http://allenbrowne.com/func...
>
> It is useful to note that ADOX was added as an extra after ADO was
> released and developers complained on the lack of DDL (Data Definition
> Language) in the ADO object model. It is obviously built over OLE DB,
> as is ADODB, but keep in mind it is NOT part of the ADODB object
> model, thus as noted in the above article can be construed as
> "unstable".
>
> This unstablility typically comes about when developers unknowningly
> start to mix objects - thinking they are dealing with the same thing
> when they are not. Besides the obvious issue of hammering on the same
> data objects with both libraries at the same time, there is the more
> subtle problems introduced when you mix both DDL Queries via ADODB,
> and ADOX DDL procedures.
>
> Best practice is to keep your "ADOX", or DDL, activities separate from
> your "ADODB" activities, i.e. have two hats; be aware at all times
> which hat you have on; and carefully swap them as needed. <g>
>
> -ralph

Thanks, Ralph. I did not know this about ADOX since I've only ever use
DAO or ADODB. Actually, I don't see where/when I'd use anything other
than those but ya' never know...

--
Garry

Free usenet access at http://www.eternal-sep...
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

(Mike Mitchell)

5/1/2012 5:08:00 PM

On Tue, 01 May 2012 11:38:32 -0500, ralph <nt_consulting64@yahoo.com>
wrote:

>On Tue, 01 May 2012 14:24:42 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>
>>At present, to link a table from an external database, I do:
>>
>/snipped code
>>
>>However, this requires a reference to DAO360.DLL. Can the same thing
>>be achieved using e.g. ADOX, and if so, how?
>>
>
>This will give you a good idea on how to use ADOX.
>
>"ADOX Programming Code Examples"
>http://allenbrowne.com/func...
>
>It is useful to note that ADOX was added as an extra after ADO was
>released and developers complained on the lack of DDL (Data Definition
>Language) in the ADO object model. It is obviously built over OLE DB,
>as is ADODB, but keep in mind it is NOT part of the ADODB object
>model, thus as noted in the above article can be construed as
>"unstable".
>
>This unstablility typically comes about when developers unknowningly
>start to mix objects - thinking they are dealing with the same thing
>when they are not. Besides the obvious issue of hammering on the same
>data objects with both libraries at the same time, there is the more
>subtle problems introduced when you mix both DDL Queries via ADODB,
>and ADOX DDL procedures.
>
>Best practice is to keep your "ADOX", or DDL, activities separate from
>your "ADODB" activities, i.e. have two hats; be aware at all times
>which hat you have on; and carefully swap them as needed. <g>
>
>-ralph

Thanks. Having changed from DAO to ADO years ago, I am loathe to
re-introduce DAO to the mix, even though it worked straightaway.

I did do some Googling before asking the question, but have yet to
find a suitable example using ADOX, although I have used ADOX for
other purposes.

MM

Ralph

5/1/2012 7:14:00 PM

On Tue, 01 May 2012 18:07:57 +0100, MM <kylix_is@yahoo.co.uk> wrote:

>
>Thanks. Having changed from DAO to ADO years ago, I am loathe to
>re-introduce DAO to the mix, even though it worked straightaway.
>

Don't be afraid.

DAO has been swamped by a ton of FUD over the years. One of the major
spreaders and leader of the parade has been Microsoft itself. It has
been pronounced dead by MS so many times, only to be quietly updated
and released.

Currently you may be surprised that MS now recommends using DAO over
ADO for Jet-formatted databases. There are a few features provided by
ADOX that DAO lacks, but in general DAO is far superior in its DDL
features and performance.

["ACE" is nothing more than 'advanced' DAO.]

>I did do some Googling before asking the question, but have yet to
>find a suitable example using ADOX, although I have used ADOX for
>other purposes.

It is a simple matter of using the best tool for the job. Barring
issues with wires and scale (which is really limitations imposed by a
file-based data store), for DDL - DAO is the tool of choice for 9 out
of 10 Jet programmers. <g>

-ralph

(Mike Mitchell)

5/2/2012 8:51:00 AM

On Tue, 01 May 2012 14:14:29 -0500, ralph <nt_consulting64@yahoo.com>
wrote:

>On Tue, 01 May 2012 18:07:57 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>
>
>>
>>Thanks. Having changed from DAO to ADO years ago, I am loathe to
>>re-introduce DAO to the mix, even though it worked straightaway.
>>
>
>Don't be afraid.
>
>DAO has been swamped by a ton of FUD over the years. One of the major
>spreaders and leader of the parade has been Microsoft itself. It has
>been pronounced dead by MS so many times, only to be quietly updated
>and released.
>
>Currently you may be surprised that MS now recommends using DAO over
>ADO for Jet-formatted databases. There are a few features provided by
>ADOX that DAO lacks, but in general DAO is far superior in its DDL
>features and performance.
>
>["ACE" is nothing more than 'advanced' DAO.]
>
>>I did do some Googling before asking the question, but have yet to
>>find a suitable example using ADOX, although I have used ADOX for
>>other purposes.
>
>It is a simple matter of using the best tool for the job. Barring
>issues with wires and scale (which is really limitations imposed by a
>file-based data store), for DDL - DAO is the tool of choice for 9 out
>of 10 Jet programmers. <g>
>
>-ralph

Oh, well, maybe I will stick with DAO, then! (However, I will use ADO
for things like disconnected recordsets, which I find incredibly
useful, plus even creating them from scratch without an mdb.)

MM

unknown

5/2/2012 3:06:00 PM

"MM" <kylix_is@yahoo.co.uk> wrote in message
news:18t1q79qto8fk8jk438n940v99j706squv@4ax.com...
> On Tue, 01 May 2012 14:14:29 -0500, ralph <nt_consulting64@yahoo.com>
> wrote:
>
>>On Tue, 01 May 2012 18:07:57 +0100, MM <kylix_is@yahoo.co.uk> wrote:
>>
>>
>>>
>>>Thanks. Having changed from DAO to ADO years ago, I am loathe to
>>>re-introduce DAO to the mix, even though it worked straightaway.
>>>
>>
>>Don't be afraid.
>>
>>DAO has been swamped by a ton of FUD over the years. One of the major
>>spreaders and leader of the parade has been Microsoft itself. It has
>>been pronounced dead by MS so many times, only to be quietly updated
>>and released.
>>
>>Currently you may be surprised that MS now recommends using DAO over
>>ADO for Jet-formatted databases. There are a few features provided by
>>ADOX that DAO lacks, but in general DAO is far superior in its DDL
>>features and performance.
>>
>>["ACE" is nothing more than 'advanced' DAO.]
>>
>>>I did do some Googling before asking the question, but have yet to
>>>find a suitable example using ADOX, although I have used ADOX for
>>>other purposes.
>>
>>It is a simple matter of using the best tool for the job. Barring
>>issues with wires and scale (which is really limitations imposed by a
>>file-based data store), for DDL - DAO is the tool of choice for 9 out
>>of 10 Jet programmers. <g>
>>
>>-ralph
>
> Oh, well, maybe I will stick with DAO, then! (However, I will use ADO
> for things like disconnected recordsets, which I find incredibly
> useful, plus even creating them from scratch without an mdb.)
>
> MM

Here are some articles that show how to use ADOX:

How To Create a Table with Primary Key Through ADOX
http://support.microsoft.com...

How To Use ADO and ADOX to Modify the Base Query of an Access QueryDef
Object in Visual Basic
http://support.microsoft.com/k...

How To Change an Access Table Name Programmatically
http://support.microsoft.com...

Ralph

5/2/2012 3:48:00 PM

On Wed, 02 May 2012 09:50:39 +0100, MM <kylix_is@yahoo.co.uk> wrote:

>
>Oh, well, maybe I will stick with DAO, then! (However, I will use ADO
>for things like disconnected recordsets, which I find incredibly
>useful, plus even creating them from scratch without an mdb.)
>

"Disconnected Recordsets" are mimicked in DAO by creating or attaching
a new database, and then managed through multiple DBEngines,
PrivDBEngines, Workspaces, or the Database Collection.

This is less convenient than disconnected ADODB Recorsets since it
requires the existence of an external file. However, this 'limitation'
can be quite useful in failure management, auditing, and transaction
scenarios. (You can share DBEngines across processes, and have up to
64 DBEngines within any one process.)

Only bringing this up to help you get over any feelings of being
"trapped" with DAO. <bg>

-ralph

Schmidt

5/2/2012 6:57:00 PM

Am 01.05.2012 15:24, schrieb MM:

> Can the same thing
> be achieved using e.g. ADOX, and if so, how?

You can use Views for that (in conjunction with the 'In' clause).

e.g. into a module which handles your Main-ADO-Cnn:

Option Explicit

Public Cnn As Connection

Public Sub OpenDB(DBFileName As String)
Set Cnn = New Connection
Cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& DBFileName
Cnn.Open
End Sub

Public Sub AttachForeignTable(TableName As String, _
ForeignDBPath As String, Optional Prefix As String = "ext_")
On Error Resume Next
Cnn.Execute "Drop View [" & Prefix & TableName & "]"
On Error Goto 0

Cnn.Execute "Create View [" & Prefix & TableName & _
"] As Select * From [" & TableName & "] In '" & ForeignDBPath & "'"
End Sub

Now you can attach on your "Main-Cnn" the foreign tables
dynamically (under a new View-Name with a free choosable
Prefix... here I've used 'ext_'):

OpenDB App.Path & "\MyMain.mdb"
AttachForeignTable "MyTable", App.Path & "\MyForeign.mdb"

In your Selects this Prefix can then be used, to address
the Foreign-Tables without ambiguity...

Here for example a Union (assuming the external DB has
a similarly named Table 'MyTable' with the same layout
as your 'MyTable' in your Main-DB):

"Select * From MyTable Union All Select * From ext_MyTable"

Olaf

Ralph

5/3/2012 4:13:00 PM

On Wed, 02 May 2012 20:56:32 +0200, Schmidt <sss@online.de> wrote:

>Am 01.05.2012 15:24, schrieb MM:
>
>> Can the same thing
>> be achieved using e.g. ADOX, and if so, how?
>
>You can use Views for that (in conjunction with the 'In' clause).
>
>e.g. into a module which handles your Main-ADO-Cnn:
>
>Option Explicit
>
>Public Cnn As Connection
>
>Public Sub OpenDB(DBFileName As String)
> Set Cnn = New Connection
> Cnn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
> & DBFileName
> Cnn.Open
>End Sub
>
>Public Sub AttachForeignTable(TableName As String, _
> ForeignDBPath As String, Optional Prefix As String = "ext_")
> On Error Resume Next
> Cnn.Execute "Drop View [" & Prefix & TableName & "]"
> On Error Goto 0
>
> Cnn.Execute "Create View [" & Prefix & TableName & _
> "] As Select * From [" & TableName & "] In '" & ForeignDBPath & "'"
>End Sub
>
>Now you can attach on your "Main-Cnn" the foreign tables
>dynamically (under a new View-Name with a free choosable
>Prefix... here I've used 'ext_'):
>
>OpenDB App.Path & "\MyMain.mdb"
>AttachForeignTable "MyTable", App.Path & "\MyForeign.mdb"
>
>In your Selects this Prefix can then be used, to address
>the Foreign-Tables without ambiguity...
>
>Here for example a Union (assuming the external DB has
>a similarly named Table 'MyTable' with the same layout
>as your 'MyTable' in your Main-DB):
>
>"Select * From MyTable Union All Select * From ext_MyTable"
>
>

A good example of using DDL via ADO and likely does what the OP is
trying to do in the example provided.

However, a little amplification or warning may be in order.

1) Views contain no data storage. They only show or return data
located elsewhere.
2) They allow updating of the data, but only under certain conditions.
3) Created Views are stored in the database, but they can only be
subsequently reached via ADO or ADOX. They are not visible in the MS
Access user interface or through DAO.

-ralph

microsoft.public.vb.general.discussion

How can I link external Access database in VB6 without requiring DAO360.dll?

(Mike Mitchell)

GS

Ralph

GS

(Mike Mitchell)

Ralph

(Mike Mitchell)

unknown

Ralph

Schmidt

Ralph

x Login to ForumsZone