create a linked table

[Gerhard Huessner]

I want to create a linked table in access.
To get this link I open a huge ORACLE database with 20.000 tables.
MS Access opens the connection and shows the names of the first 150 tables
in a list of values to select to which table the linked table should point.
But my needed table is not included in the first 150 shown in th LOV.

How an I get the table linked?

- Gerhard -

 

[Gerhard Huessner]

Does really no one has an idea?

 

[Brendan Reynolds]

I'm afraid databases with 20,000 tables are probably outside most people's
experience, Gerhard. They're certainly outside mine.

Perhaps you could work around the problem by creating the links
programmatically. You might find the code at the following URL useful ...

http://www.mvps.org/access/tables/tbl0010.htm

 

[Gerhard Huessner]

Thank you Brendan for your reply!

Yes - It might be a work around to do it programmaticaly.
Sorry, but I have no idea where I could run this code.
Can you help me?

- Gerhard -

P.S.: It's a ORACLE 11i database.

 

[Brendan Reynolds]

If this is a once-off job, possibly that code might be more complex than you
need. There's an MSDN article at the following URL that has a simpler
example of how to programmatically create a linked table ...

http://msdn.microsoft.com/archive/d...e97/html/establishingaccesstoexternaldata.asp

Although this is archived Access 97 content, I don't think there is anything
in the example that isn't still valid today.

I have no Oracle experience, so I'm afraid I will not be able to help with
any Oracle-specific modifications that might be required.

 

[Gerhard Huessner]

Hello Brenden,

Thank you for trying to help me with my issue to link an ORACLE table in MS
ACCESS!
I also think the code you told me should work to solve my issue.

I need no help on the ORACLE side, there I have enough experience to get it
work. But on the MS ACCESS side I have no experience to run any code.
I opened the window for entering Makros and tried to get the code running
there.
But the only response was something like "user defined-type not defined" in
the first line of the variable definitions!?!?
Is there any other possibility to run Visual Basic code?

- Gerhard -

 

[Brendan Reynolds]

In some Office applications, the term 'macro' is used synonymously with VBA
code, but in Access macros and code are separate and very different. You
need to create a VBA standard module, not a macro. You can create a VBA
standard module by choosing Module from the Insert menu. You might find it
useful to review some of the articles at the following URL first ...

http://office.microsoft.com/en-gb/assistance/CH063664351033.aspx

The 'user-defined-type not defined' error message may mean that you need to
set a reference to the DAO object library. When you have created a VBA
standard module, in the VBA editor select References from the Tools menu. In
the list of available references, find Microsoft DAO 3.6 Object Library. If
there isn't a tick mark in the check box beside that reference, click once
in the check box to select it, then click OK to close the References dialog
box.

 

[Gerhard Huessner]

Brendan,

The code seems to be running using this "module" menue item.
But I get still an error "Run-time error 3170; Could not find installabel
ISAM".

My code is:

Sub LinkFoxProTable()
Dim dbs As Database
Dim tdfSales As TableDef
Dim rst As Recordset

' Open the Microsoft Access database.
Set dbs = CurrentDb

' Create a TableDef object.
Set tdfSales = dbs.CreateTableDef("XX_GLOBAL_BUDGET")

' Set the connection string to specify the source database type and the
' path to the file that contains the table you want to link.
tdfSales.Connect = "Provider=MSDASQL.1;Data Source=PROD2;"

' Set the SourceTableName property to the name of the table you want to
access.
tdfSales.SourceTableName = "HOR_CUS.XX_GLOBAL_BUDGET"

' Append the TableDef object to the TableDefs collection to create a link.
dbs.TableDefs.Append tdfSales

' Create a Recordset object from the linked table.
Set rst = dbs.OpenRecordset("XX_GLOBAL_BUDGET")
End Sub

The System DSN "PROD" ist installed and okay.

Do you have an idea of the reason for this error?

- Gerhard -

 

[Brendan Reynolds]

That error message, under these circumstances, in my experience usually
means that Access does not recognize the connection string. Access seems to
default to assuming that the connection string specifies an ISAM data source
if it can not recognize it as anything else.

Carl Prothman has examples of many different connection strings at this site
....

http://www.carlprothman.net/Default.aspx?tabid=81

He has examples for ODBC DSNs at ...

http://www.carlprothman.net/Default.aspx?tabid=89

.... and DSN-less examples for Oracle at ...

http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForOracleFromMicrosoft

(The above example uses Microsoft's ODBC driver for Oracle, but the example
using Oracle's driver is just below it on the same page).

 

[Douglas J. Steele]

One thing that Carl doesn't mention (since I believe it's unique to Access)
is the fact that the DSN-less connection string must start with ODBC;

I believe this is true for DSN-based connection strings as well.

 

[Brendan Reynolds]

Thanks Doug.

--
Brendan Reynolds
Access MVP

One thing that Carl doesn't mention (since I believe it's unique to
Access) is the fact that the DSN-less connection string must start with
ODBC;

I believe this is true for DSN-based connection strings as well.

 

[Gerhard Huessner]

Thank you Brendan, thank you Doug,

We can close the thread because I setup a ORACLE user with limited access.
The new ORACLE user has only access to one table.
Now I can use the usual procedure to open a linked Table and I get only this
table
in the list of values to select.
Now it works perfect.

- Gerhard -