ADOX and Access Linked Tables

ADOX and Access Linked Tables

Post by Val Mazu » Thu, 21 Nov 2002 04:04:48



Shawn,

Do you have Jet 4.0 installed on your PC? Also check next KB about creating
links using ADOX

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q230588

--
Val Mazur
Microsoft MVP


Quote:> Hi all

> I was wondering if anyone managed to add a linked SQL
> Server table in Access using ADOX. I added one manually
> and than enumerated Properties collection of the Table
> object in order to make sure that I'm setting up
> properties correctly before I add the table. Didn't work
> although all my properties were identical to the manually
> set-up linked table. The error comming back is "Can not
> find installable ISAM" which leads me to beleive that only
> ISAM drivers can be used for this. The odd thing is that
> even if I use a DSN I get the same error (I would like to
> do this OLEDB but no luck). DAO version works without a
> problem.

> Thanks

> Shawn

 
 
 

ADOX and Access Linked Tables

Post by Shawn Kral » Thu, 21 Nov 2002 06:03:51


hi Val

The Jet I have installed is version 4 (Office 2000). I
followed the link you gave me but it only talks about
linking another Access table. I want to link an MS SQL
table and that is what I'm having trouble with. If I
understand corrrectly the only thing that should change
from the example you gave me (on the web link) is the
Properties("JET OLEDB:Link Provider String").Value and it
should be an SQL Server connection string. Is this
correct? I did this and it did not work. I get the same
error.

Thanks for the quick response

Shawn

>-----Original Message-----
>Shawn,

>Do you have Jet 4.0 installed on your PC? Also check next
KB about creating
>links using ADOX

>http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q230588

>--
>Val Mazur
>Microsoft MVP



>> Hi all

>> I was wondering if anyone managed to add a linked SQL
>> Server table in Access using ADOX. I added one manually
>> and than enumerated Properties collection of the Table
>> object in order to make sure that I'm setting up
>> properties correctly before I add the table. Didn't work
>> although all my properties were identical to the
manually
>> set-up linked table. The error comming back is "Can not
>> find installable ISAM" which leads me to beleive that
only
>> ISAM drivers can be used for this. The odd thing is that
>> even if I use a DSN I get the same error (I would like
to
>> do this OLEDB but no luck). DAO version works without a
>> problem.

>> Thanks

>> Shawn

>.


 
 
 

ADOX and Access Linked Tables

Post by Val Mazu » Thu, 21 Nov 2002 22:25:54


Shawn,

Yes, it should point ot connection string.
Also check next example with linking to SQL Server

http://www.able-consulting.com/ADOX_Faq.htm#Q9

--
Val Mazur
Microsoft MVP


> hi Val

> The Jet I have installed is version 4 (Office 2000). I
> followed the link you gave me but it only talks about
> linking another Access table. I want to link an MS SQL
> table and that is what I'm having trouble with. If I
> understand corrrectly the only thing that should change
> from the example you gave me (on the web link) is the
> Properties("JET OLEDB:Link Provider String").Value and it
> should be an SQL Server connection string. Is this
> correct? I did this and it did not work. I get the same
> error.

> Thanks for the quick response

> Shawn

> >-----Original Message-----
> >Shawn,

> >Do you have Jet 4.0 installed on your PC? Also check next
> KB about creating
> >links using ADOX

> >http://support.microsoft.com/default.aspx?scid=kb;en-
> us;Q230588

> >--
> >Val Mazur
> >Microsoft MVP



> >> Hi all

> >> I was wondering if anyone managed to add a linked SQL
> >> Server table in Access using ADOX. I added one manually
> >> and than enumerated Properties collection of the Table
> >> object in order to make sure that I'm setting up
> >> properties correctly before I add the table. Didn't work
> >> although all my properties were identical to the
> manually
> >> set-up linked table. The error comming back is "Can not
> >> find installable ISAM" which leads me to beleive that
> only
> >> ISAM drivers can be used for this. The odd thing is that
> >> even if I use a DSN I get the same error (I would like
> to
> >> do this OLEDB but no luck). DAO version works without a
> >> problem.

> >> Thanks

> >> Shawn

> >.

 
 
 

1. ADOX and Access linked tables

HELP!!

I have an Access 97 database that is either attached to another Access 97
database or a SQL Server 6.5 database via ODBC.

I have a VB6 procedure that attempts to refresh the linked tables after
connection to the database, as the database that the tables are linked to
may have moved.

If my database is linked to another Access 97 database, the following code
works...

Public Sub RefreshLinks()

    Dim cat As New adox.Catalog
    Dim tbl As Table

    Set cat.ActiveConnection = mcnnConn

    For Each tbl In cat.Tables
        If tbl.Type = "LINK" Then
            tbl.Properties("Jet OLEDB:Link Datasource") =
"C:\DatabasePath\DatabaseName.mdb"
        End If
    Next tbl

    Set tbl = Nothing
    Set cat = Nothing

End Sub

My problem is trying to refresh tables that are linked to a SQL Server
database.

I have noticed that the "Jet OLEDB:Link Datasource" property, used above, is
not relevent for SQL Server databases as it is blank. However the "Jet
OLEDB:Link Provider String" property of the tables is set to the following:

DRIVER=SQL Server;SERVER=BRITAIN;APP=Microsoft?
Access;WSID=CAIRO;DATABASE=EXAMPLE

I assumed that what I needed to do was set this property to the relevent
SERVER or DATABASE, i.e.

    For Each tbl In cat.Tables
        If tbl.Type = "PASS-THROUGH" Then
            tbl.Properties("Jet OLEDB:Link Provider String") = "DRIVER=SQL
Server;SERVER=FRANCE;APP=Microsoft? Access;WSID=CAIRO;DATABASE=EXAMPLE2"
        End If
    Next tbl

This causes the following error on the tbl.properties.... line:
ODBC--connection to 'SQL ServerBRITAIN' failed. [ref. FFFF8000:16389 in
Microsoft JET Database Engine].
(I have tested the ODBC connection and I know that it works).

I tried instead to delete the table and add a new one using the following
code:

            cat.Tables.Delete "oldTable"
            Set tbl = New Table
            tbl.Name = "newTable"
            Set tbl.ParentCatalog = cat
            tbl.Properties("Jet OLEDB:Remote Table Name") = "dbo.TableName"
            tbl.Properties("Jet OLEDB:Link Provider String") = "DRIVER=SQL
Server;SERVER=FRANCE;APP=Microsoft? Access;WSID=CAIRO;DATABASE=EXAMPLE2"
            cat.Tables.Append tbl

This appears to work but what it actually does is create a table of type
TABLE and not PASS-THROUGH and there are no columns. I know that you can add
columns by using .Columns.Append but I don't want to do this, I want it to
be linked to the SQL Server table thus inheriting the columns from that.

HELP PLEASE, IT'S DRIVING ME MAD!!

2. sql certificates

3. ADOX and Access Linked Tables

4. Lottery Information

5. Re-linking Access tables with ADOX

6. Data form wizard

7. ADOX Indexes on Access Linked Tables...

8. Visual DB Tools in VB5?

9. ADOX : Linking Access and SQL Server leaves Access read only

10. ADOX link to Access.MDE problem!!

11. Using ADOX to create linked table

12. ADOX.Append Creates a Read-Only Linked Table to SQL Server 7.0

13. Linking a Foxpro table with Adox