ADOX Indexes on Access Linked Tables...

ADOX Indexes on Access Linked Tables...

Post by John Robert » Thu, 10 Oct 2002 03:49:34



Hello ADO Gurus

I've written some code to go through my Oracle database and create linked
tables, or if necessary to update the link properties. This works fine,
however I need to create indexes on the linked tables to allow my apps to
update records through Access. Try as I might, every time I try to
programmatically create an index for a linked table the procedure fails,
with "Invalid Argument".

Has anyone out there run into this before? I'd really like my app to be able
to generate new linked tables with indexes, such that the underlying tables
in oracle can be edited through Access.

Thanks in Advance

- John

Sub RefreshLinks(strDBLinkFrom As String, _

strDBLinkSource As String)

Dim oCat As ADOX.Catalog

Dim oTable As ADOX.Table

Dim sConnString As String

Dim idx As ADOX.Index

Dim columns As ADOX.columns

Dim column As ADOX.column

sConnString = "DSN=PUOPS;" & _

"Uid=puamfm;" & _

"Pwd=puamfm"

oConn.Open sConnString

' Create and open an ADOX connection to Access database

Set oCat = New ADOX.Catalog

oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & g_strDB & ";"

For Each oTable In oCat.Tables

Debug.Print oTable.Name

If oTable.Indexes.Count = 0 Then 'not updateable

For Each column In oTable.columns

'If oTable.Type = "TABLE" Then

Select Case UCase(column.Name)

Case "MSLINK"

Set idx = New ADOX.Index

idx.Name = "__" & oTable.Name & column.Name

idx.columns.Append column.Name

idx.Unique = True

oTable.Indexes.Append idx

Case "QUADNAME"

Set idx = New ADOX.Index

idx.Name = oTable.Name & "_" & column.Name

idx.columns.Append column.Name

oTable.Indexes.Append idx

End Select

'End If

Next column

End If

If oTable.Type = "PASS-THROUGH" Then

Debug.Print oTable.Name

With oTable

'.Properties("Jet OLEDB:Create Link") = False

'.Properties("Jet OLEDB:Remote Table Name") = "authors"

.Properties("Jet OLEDB:Link Provider String") = sConnString

End With

End If

Next

oCat.Tables.Refresh

 
 
 

ADOX Indexes on Access Linked Tables...

Post by clinton » Thu, 10 Oct 2002 04:12:45


I see you are already using the OLEDB provider but likely
incorrectly. Have you downloaded the MDAC SDK where
you may discover examples of method calls describing the
correct syntax?

SEE: http://www.microsoft.com/data/

I've also been finding it helpful to refer to the
WROX ADO 2.6 Programmer's Reference.

Lastly...

Search MSDN: "ADOX Code Examples in Microsoft Visual Basic"

--
<%= Clinton Gallagher
    A/E/C Consulting, Web Design, e-Commerce Software Development
    Wauwatosa, Milwaukee County, Wisconsin USA

    URL http://www.metromilwaukee.com/clintongallagher/

    LaGarde StoreFront 5 Affiliate: e-Commerce Software Development
    SEE: http://www.storefront.net/default.asp?-201499070


Quote:> Hello ADO Gurus

> I've written some code to go through my Oracle database and create linked
> tables, or if necessary to update the link properties. This works fine,
> however I need to create indexes on the linked tables to allow my apps to
> update records through Access. Try as I might, every time I try to
> programmatically create an index for a linked table the procedure fails,
> with "Invalid Argument".

> Has anyone out there run into this before? I'd really like my app to be
able
> to generate new linked tables with indexes, such that the underlying
tables
> in oracle can be edited through Access.

> Thanks in Advance

> - John

> Sub RefreshLinks(strDBLinkFrom As String, _

> strDBLinkSource As String)

> Dim oCat As ADOX.Catalog

> Dim oTable As ADOX.Table

> Dim sConnString As String

> Dim idx As ADOX.Index

> Dim columns As ADOX.columns

> Dim column As ADOX.column

> sConnString = "DSN=PUOPS;" & _

> "Uid=puamfm;" & _

> "Pwd=puamfm"

> oConn.Open sConnString

> ' Create and open an ADOX connection to Access database

> Set oCat = New ADOX.Catalog

> oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

> "Data Source=" & g_strDB & ";"

> For Each oTable In oCat.Tables

> Debug.Print oTable.Name

> If oTable.Indexes.Count = 0 Then 'not updateable

> For Each column In oTable.columns

> 'If oTable.Type = "TABLE" Then

> Select Case UCase(column.Name)

> Case "MSLINK"

> Set idx = New ADOX.Index

> idx.Name = "__" & oTable.Name & column.Name

> idx.columns.Append column.Name

> idx.Unique = True

> oTable.Indexes.Append idx

> Case "QUADNAME"

> Set idx = New ADOX.Index

> idx.Name = oTable.Name & "_" & column.Name

> idx.columns.Append column.Name

> oTable.Indexes.Append idx

> End Select

> 'End If

> Next column

> End If

> If oTable.Type = "PASS-THROUGH" Then

> Debug.Print oTable.Name

> With oTable

> '.Properties("Jet OLEDB:Create Link") = False

> '.Properties("Jet OLEDB:Remote Table Name") = "authors"

> .Properties("Jet OLEDB:Link Provider String") = sConnString

> End With

> End If

> Next

> oCat.Tables.Refresh


 
 
 

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. DTS & Data Truncation on Export to Excel

3. ADOX and Access Linked Tables

4. Websql Output

5. Re-linking Access tables with ADOX

6. US-Contract/Perm. Opportunities

7. ADOX and Access Linked Tables

8. Wanted: BDE API info

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

10. ADOX.Indexes/ADOX.Key/Foreign Keys

11. Using ADOX to create linked table