Linked tables using ADO instead of DAO? 
Author Message
 Linked tables using ADO instead of DAO?

I currently use DAO to connect linked tables

        Set tdf = db.CreateTableDef(strPrefix & rs![Name])
        tdf.Connect = dataSource
        tdf.SourceTableName = rs![Name]
        db.TableDefs.Append tdf

Is there a way to use ADO to do the same thing? (If yes, a code snippet or
point me to some place to get the code would be helpful).

Is there a way to use Datalinks (UDL files) to link to ODBC databases using
ADO as well?
(I would prefer to popup the Datalink dialog box for linking to DSNs instead
of the standard ODBC dialog).

Thanks.

--
Andrew D. Fields

Fields & Associates Consulting, Inc.



Tue, 16 Dec 2003 03:58:53 GMT  
 Linked tables using ADO instead of DAO?

I don't believe you can since, AFAIK, linking tables can only be done in a
jet workspace.

HTH

Iskander


Quote:
> I currently use DAO to connect linked tables

>         Set tdf = db.CreateTableDef(strPrefix & rs![Name])
>         tdf.Connect = dataSource
>         tdf.SourceTableName = rs![Name]
>         db.TableDefs.Append tdf

> Is there a way to use ADO to do the same thing? (If yes, a code snippet or
> point me to some place to get the code would be helpful).

> Is there a way to use Datalinks (UDL files) to link to ODBC databases
using
> ADO as well?
> (I would prefer to popup the Datalink dialog box for linking to DSNs
instead
> of the standard ODBC dialog).

> Thanks.

> --
> Andrew D. Fields

> Fields & Associates Consulting, Inc.



Tue, 16 Dec 2003 04:31:54 GMT  
 Linked tables using ADO instead of DAO?
HOWTO: Link and Refresh Linked Jet Tables Using ADOX  Ms Kb: Q230588  

Steve

Quote:

> I don't believe you can since, AFAIK, linking tables can only be done in a
> jet workspace.

> HTH

> Iskander



> > I currently use DAO to connect linked tables

> >         Set tdf = db.CreateTableDef(strPrefix & rs![Name])
> >         tdf.Connect = dataSource
> >         tdf.SourceTableName = rs![Name]
> >         db.TableDefs.Append tdf

> > Is there a way to use ADO to do the same thing? (If yes, a code snippet or
> > point me to some place to get the code would be helpful).

> > Is there a way to use Datalinks (UDL files) to link to ODBC databases
> using
> > ADO as well?
> > (I would prefer to popup the Datalink dialog box for linking to DSNs
> instead
> > of the standard ODBC dialog).

> > Thanks.

> > --
> > Andrew D. Fields

> > Fields & Associates Consulting, Inc.



Tue, 16 Dec 2003 05:03:38 GMT  
 Linked tables using ADO instead of DAO?

Thanks

HOWTO: Link and Refresh Linked Jet Tables Using ADOX  Ms Kb: Q230588

Steve


Quote:

> I don't believe you can since, AFAIK, linking tables can only be done in a
> jet workspace.

> HTH

> Iskander



> > I currently use DAO to connect linked tables

> >         Set tdf = db.CreateTableDef(strPrefix & rs![Name])
> >         tdf.Connect = dataSource
> >         tdf.SourceTableName = rs![Name]
> >         db.TableDefs.Append tdf

> > Is there a way to use ADO to do the same thing? (If yes, a code snippet
or
> > point me to some place to get the code would be helpful).

> > Is there a way to use Datalinks (UDL files) to link to ODBC databases
> using
> > ADO as well?
> > (I would prefer to popup the Datalink dialog box for linking to DSNs
> instead
> > of the standard ODBC dialog).

> > Thanks.

> > --
> > Andrew D. Fields

> > Fields & Associates Consulting, Inc.



Tue, 16 Dec 2003 05:30:07 GMT  
 Linked tables using ADO instead of DAO?
Thank you Steve!

I'll take a look and see if that provides what I need.

--
Andrew D. Fields

Fields & Associates Consulting, Inc.

HOWTO: Link and Refresh Linked Jet Tables Using ADOX  Ms Kb: Q230588

Steve


Quote:

> I don't believe you can since, AFAIK, linking tables can only be done in a
> jet workspace.

> HTH

> Iskander



> > I currently use DAO to connect linked tables

> >         Set tdf = db.CreateTableDef(strPrefix & rs![Name])
> >         tdf.Connect = dataSource
> >         tdf.SourceTableName = rs![Name]
> >         db.TableDefs.Append tdf

> > Is there a way to use ADO to do the same thing? (If yes, a code snippet
or
> > point me to some place to get the code would be helpful).

> > Is there a way to use Datalinks (UDL files) to link to ODBC databases
> using
> > ADO as well?
> > (I would prefer to popup the Datalink dialog box for linking to DSNs
> instead
> > of the standard ODBC dialog).

> > Thanks.

> > --
> > Andrew D. Fields

> > Fields & Associates Consulting, Inc.



Tue, 16 Dec 2003 05:35:27 GMT  
 Linked tables using ADO instead of DAO?
Steve:

Here is the code I used (modified to point to the Active Projects's
Connection).
The example points to a separate database, but if in Access you want to
connect to the CURRENT Database, you can use CurrentProject.Connection.

I just thought I'd put the code here in case anyone wanted to test it.

--
Andrew D. Fields

Fields & Associates Consulting, Inc.

    Dim cn As ADODB.Connection
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    Set cn = New ADODB.Connection
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table

    ' Open Catalog
    cat.ActiveConnection = CurrentProject.Connection

    ' Create the Table
    tbl.Name = "Linked_Employees"
    Set tbl.ParentCatalog = cat

    ' Set the properties of the link
    tbl.Properties("JET OLEDB:Link Datasource") = "D:\Program
Files\MSOffice2k\Office\Samples\Northwind.mdb"
    tbl.Properties("Jet OLEDB:Remote Table Name") = "Employees"
    tbl.Properties("Jet OLEDB:Create Link") = True

    ' Append the table to Tables Collection
    cat.Tables.Append tbl

    Set cat = Nothing


Quote:
> Thank you Steve!

> I'll take a look and see if that provides what I need.

> --
> Andrew D. Fields

> Fields & Associates Consulting, Inc.


> HOWTO: Link and Refresh Linked Jet Tables Using ADOX  Ms Kb: Q230588

> Steve



> > I don't believe you can since, AFAIK, linking tables can only be done in
a
> > jet workspace.

> > HTH

> > Iskander



> > > I currently use DAO to connect linked tables

> > >         Set tdf = db.CreateTableDef(strPrefix & rs![Name])
> > >         tdf.Connect = dataSource
> > >         tdf.SourceTableName = rs![Name]
> > >         db.TableDefs.Append tdf

> > > Is there a way to use ADO to do the same thing? (If yes, a code
snippet
> or
> > > point me to some place to get the code would be helpful).

> > > Is there a way to use Datalinks (UDL files) to link to ODBC databases
> > using
> > > ADO as well?
> > > (I would prefer to popup the Datalink dialog box for linking to DSNs
> > instead
> > > of the standard ODBC dialog).

> > > Thanks.

> > > --
> > > Andrew D. Fields

> > > Fields & Associates Consulting, Inc.



Tue, 16 Dec 2003 06:33:32 GMT  
 Linked tables using ADO instead of DAO?
The "I don't think you can" related to the UDL request, I believe.

--
MichKa

the only book on internationalization in VB at
http://www.i18nWithVB.com/


Quote:
> Steve:

> Here is the code I used (modified to point to the Active Projects's
> Connection).
> The example points to a separate database, but if in Access you want to
> connect to the CURRENT Database, you can use CurrentProject.Connection.

> I just thought I'd put the code here in case anyone wanted to test it.

> --
> Andrew D. Fields

> Fields & Associates Consulting, Inc.

>     Dim cn As ADODB.Connection
>     Dim cat As ADOX.Catalog
>     Dim tbl As ADOX.Table

>     Set cn = New ADODB.Connection
>     Set cat = New ADOX.Catalog
>     Set tbl = New ADOX.Table

>     ' Open Catalog
>     cat.ActiveConnection = CurrentProject.Connection

>     ' Create the Table
>     tbl.Name = "Linked_Employees"
>     Set tbl.ParentCatalog = cat

>     ' Set the properties of the link
>     tbl.Properties("JET OLEDB:Link Datasource") = "D:\Program
> Files\MSOffice2k\Office\Samples\Northwind.mdb"
>     tbl.Properties("Jet OLEDB:Remote Table Name") = "Employees"
>     tbl.Properties("Jet OLEDB:Create Link") = True

>     ' Append the table to Tables Collection
>     cat.Tables.Append tbl

>     Set cat = Nothing



> > Thank you Steve!

> > I'll take a look and see if that provides what I need.

> > --
> > Andrew D. Fields

> > Fields & Associates Consulting, Inc.


> > HOWTO: Link and Refresh Linked Jet Tables Using ADOX  Ms Kb: Q230588

> > Steve



> > > I don't believe you can since, AFAIK, linking tables can only be done
in
> a
> > > jet workspace.

> > > HTH

> > > Iskander



> > > > I currently use DAO to connect linked tables

> > > >         Set tdf = db.CreateTableDef(strPrefix & rs![Name])
> > > >         tdf.Connect = dataSource
> > > >         tdf.SourceTableName = rs![Name]
> > > >         db.TableDefs.Append tdf

> > > > Is there a way to use ADO to do the same thing? (If yes, a code
> snippet
> > or
> > > > point me to some place to get the code would be helpful).

> > > > Is there a way to use Datalinks (UDL files) to link to ODBC
databases
> > > using
> > > > ADO as well?
> > > > (I would prefer to popup the Datalink dialog box for linking to DSNs
> > > instead
> > > > of the standard ODBC dialog).

> > > > Thanks.

> > > > --
> > > > Andrew D. Fields

> > > > Fields & Associates Consulting, Inc.



Tue, 16 Dec 2003 06:37:28 GMT  
 Linked tables using ADO instead of DAO?
Linked ODBC tables tend to share the same database connection (I'm not sure of the
mechanics)  This makes it tricky to change connection properties using DAO. (You
have to make sure the connection is closed before you can modify the connect string
on any of your linked tables)

Is the same true of ADOX?

(david)

HOWTO: Link and Refresh Linked Jet Tables Using ADOX  Ms Kb: Q230588

Steve

Quote:

> I don't believe you can since, AFAIK, linking tables can only be done in a
> jet workspace.

> HTH

> Iskander



> > I currently use DAO to connect linked tables

> >         Set tdf = db.CreateTableDef(strPrefix & rs![Name])
> >         tdf.Connect = dataSource
> >         tdf.SourceTableName = rs![Name]
> >         db.TableDefs.Append tdf

> > Is there a way to use ADO to do the same thing? (If yes, a code snippet or
> > point me to some place to get the code would be helpful).

> > Is there a way to use Datalinks (UDL files) to link to ODBC databases
> using
> > ADO as well?
> > (I would prefer to popup the Datalink dialog box for linking to DSNs
> instead
> > of the standard ODBC dialog).

> > Thanks.

> > --
> > Andrew D. Fields

> > Fields & Associates Consulting, Inc.



Wed, 17 Dec 2003 12:12:06 GMT  
 Linked tables using ADO instead of DAO?
Not only is the same true, but its slightly worse than that because linked
ODBC tables created through ADOX are not updateable like they are with DAO
(if there is a unique index).

--
MichKa

the only book on internationalization in VB at
http://www.i18nWithVB.com/



Quote:
> Linked ODBC tables tend to share the same database connection (I'm not
sure of the
> mechanics)  This makes it tricky to change connection properties using
DAO. (You
> have to make sure the connection is closed before you can modify the
connect string
> on any of your linked tables)

> Is the same true of ADOX?

> (david)


> HOWTO: Link and Refresh Linked Jet Tables Using ADOX  Ms Kb: Q230588

> Steve




- Show quoted text -

Quote:

> > I don't believe you can since, AFAIK, linking tables can only be done in
a
> > jet workspace.

> > HTH

> > Iskander



> > > I currently use DAO to connect linked tables

> > >         Set tdf = db.CreateTableDef(strPrefix & rs![Name])
> > >         tdf.Connect = dataSource
> > >         tdf.SourceTableName = rs![Name]
> > >         db.TableDefs.Append tdf

> > > Is there a way to use ADO to do the same thing? (If yes, a code
snippet or
> > > point me to some place to get the code would be helpful).

> > > Is there a way to use Datalinks (UDL files) to link to ODBC databases
> > using
> > > ADO as well?
> > > (I would prefer to popup the Datalink dialog box for linking to DSNs
> > instead
> > > of the standard ODBC dialog).

> > > Thanks.

> > > --
> > > Andrew D. Fields

> > > Fields & Associates Consulting, Inc.



Wed, 17 Dec 2003 12:31:37 GMT  
 
 [ 9 post ] 

 Relevant Pages 

1. Linking Excel As Tables Using DAO

2. Strange ODBC errors using DAO to access linked tables

3. VB using DAO to execute Access query containing a linked table

4. Linking Tables from SQL 7 to Acces using DAO

5. LINKING TABLES using DAO and SQL

6. Linking Tables from SQL 7 to Acces using DAO

7. LINKING TABLES using DAO and SQL ?

8. Creating dBase tables using ADO or DAO ??

9. Determine if a table is linked using ADO

10. Link FoxPro Table in Access 2000 using ADO

11. Link SQL server table to Access using ADO

12. Linking an Oracle table to an Access97 db using ADO

 

 
Powered by phpBB® Forum Software