Linking a Foxpro table with Adox

Linking a Foxpro table with Adox

Post by Brad Emle » Thu, 17 Aug 2000 04:00:00



I have found the way to create a linked table with Sql which resolves
the issues I've posted about before.  But now I need to create a linked
table for FoxPro or Dbase, I really don't care which.  Can anyone help
me?  I've provide the code I'm using below to show both the Sql and the
attempt at Foxpro.

*** -> This Works with Sql7
Sub createlinktable()
   Dim cat As New ADOX.Catalog
   Dim tbl As New ADOX.Table
   '
   Dim StrAccessConnection As String
   Dim Sql7Connection As String
   '
   StrAccessConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & "D:\TempVfpTest\Testing.mdb" & ";Persist Security Info=False"
   Sql7Connection = "ODBC;DRIVER=SQL
Server;UID=User;AnsiNPW=No;Trusted_Connection=Yes;Database=Nbr;WSID=Mine
;APP=Microsoft? Access;SERVER=MyServer;Address=207.59.25.310"
   '
   cat.ActiveConnection = StrAccessConnection
   tbl.Name = "CtDir"
   '
   Set tbl.ParentCatalog = cat
   '
   tbl.Properties("Jet OLEDB:Create Link") = True
   tbl.Properties("Jet OLEDB:Link Provider String") = Sql7Connection
   tbl.Properties("Jet OLEDB:Remote Table Name") = "Ct_Dir"
   '
   cat.Tables.Append tbl
   '
   cat.Tables.Refresh
   Set cat = Nothing
   '
End Sub

Sub createFoxlinktable()
   '
   Dim cat As New ADOX.Catalog
   Dim tbl As New ADOX.Table

   Dim StrAccessConnection As String
   Dim StrFoxConnection As String
   '
   StrAccessConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & "D:\NewAdoTest\NbrWork.mdb" & ";Persist Security Info=False;"
   StrFoxConnection = "ODBC;Driver=Microsoft Visual FoxPro
Driver;UID=;SourceDB=e:
\;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Y
es;Deleted=Yes;"

   cat.ActiveConnection = StrAccessConnection
   tbl.Name = "o0000127"
   '
   Set tbl.ParentCatalog = cat
   '
   tbl.Properties("Jet OLEDB:Create Link") = True
   tbl.Properties("Jet OLEDB:Link Provider String") = StrFoxConnection
   tbl.Properties("Jet OLEDB:Link Datasource") = "e:\"
   'tbl.Properties("Jet OLEDB:Engine Type") = 11
   tbl.Properties("Jet OLEDB:Remote Table Name") = "O0000127.dbf"
   '
   cat.Tables.Append tbl
   '
   cat.Tables.Refresh
   Set cat = Nothing
   '
' a copy of the DSN
'[ODBC]
'DRIVER=Microsoft Visual FoxPro Driver
'UID=
'Deleted = Yes
'Null=Yes
'Collate = Machine
'BackgroundFetch = Yes
'Exclusive = No
'SourceType = DBF
'SourceDB=e:\
--

 
 
 

Linking a Foxpro table with Adox

Post by Brad Emle » Sat, 19 Aug 2000 04:00:00


I have found the answer for those who would like to know.  Presently
only Jet 4.0 and Sql providers support linking with Adox.



Quote:> I have found the way to create a linked table with Sql which resolves
> the issues I've posted about before.  But now I need to create a linked
> table for FoxPro or Dbase, I really don't care which.  Can anyone help
> me?  I've provide the code I'm using below to show both the Sql and the
> attempt at Foxpro.

> *** -> This Works with Sql7
> Sub createlinktable()
>    Dim cat As New ADOX.Catalog
>    Dim tbl As New ADOX.Table
>    '
>    Dim StrAccessConnection As String
>    Dim Sql7Connection As String
>    '
>    StrAccessConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & "D:\TempVfpTest\Testing.mdb" & ";Persist Security Info=False"
>    Sql7Connection = "ODBC;DRIVER=SQL
> Server;UID=User;AnsiNPW=No;Trusted_Connection=Yes;Database=Nbr;WSID=Mine
> ;APP=Microsoft? Access;SERVER=MyServer;Address=207.59.25.310"
>    '
>    cat.ActiveConnection = StrAccessConnection
>    tbl.Name = "CtDir"
>    '
>    Set tbl.ParentCatalog = cat
>    '
>    tbl.Properties("Jet OLEDB:Create Link") = True
>    tbl.Properties("Jet OLEDB:Link Provider String") = Sql7Connection
>    tbl.Properties("Jet OLEDB:Remote Table Name") = "Ct_Dir"
>    '
>    cat.Tables.Append tbl
>    '
>    cat.Tables.Refresh
>    Set cat = Nothing
>    '
> End Sub

> Sub createFoxlinktable()
>    '
>    Dim cat As New ADOX.Catalog
>    Dim tbl As New ADOX.Table

>    Dim StrAccessConnection As String
>    Dim StrFoxConnection As String
>    '
>    StrAccessConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & "D:\NewAdoTest\NbrWork.mdb" & ";Persist Security Info=False;"
>    StrFoxConnection = "ODBC;Driver=Microsoft Visual FoxPro
> Driver;UID=;SourceDB=e:
> \;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Y
> es;Deleted=Yes;"

--


 
 
 

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. call for participation

3. Using ADOX to create linked table

4. calling IDataInitialize::LoadStringFromStorage from VB

5. ADOX & Linked table

6. - jw009617- Portland, Oregon - Oracle DBA O

7. Re-linking Access tables with ADOX

8. CO Denver/Boulder...Sr. UNIX Systems Administrator!!!

9. ADOX and Access Linked Tables

10. Using ADOX to Link Tables

11. ADOX Linking external tables in VB

12. ADOX: Create FoxPro Table Attachment to Jet?

13. Linking Tables, ADOX 2.1