Access error creating linked Oracle table

Question

I try to create a linked table pointing to Oracle. To make it simple, I "stole" the connection string from a manually created linked table that is working well. The code breaks in the Append, where indicated below. Any clue ? Thanks !

Sub CreaOra()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim strConn As String
    Set db = CurrentDb
    'connection string copied from a working linked table !'
    strConn = "ODBC;DRIVER={Oracle in OraClient10g_home1};SERVER=ORAJJJ0;UID=xxx;PWD=yyy;DBQ=ORAWOD0;"
    Set td = db.CreateTableDef(Name:="test", SourceTableName:="ORAJJJC01.TBL_MYTBL", Connect:=strConn)
    'next row -> error 3264 No field defined--cannot append TableDef or Index
    db.TableDefs.Append td

    Set td = Nothing
    Set db = Nothing
End Sub

Conrad Frix · Accepted Answer · 2011-09-30 18:47:07Z

3

The attributes parameter must not be empty. You need to pass 0 or dbAttachSavePWD

  Set td = db.CreateTableDef(Name:="test", Attributes:=0,  SourceTableName:="ORAJJJC01.TBL_MYTBL", Connect:=strConn)

Or you can do this if you don't want to explicitly set it to 0

   Set td = db.CreateTableDef("test")
   td.SourceTableName:="ORAJJJC01.TBL_MYTBL"
   td.Connect:=strConn

Note: If you look at the td in the watch window can observe that the Connect and SourceTableName get set to empty if you don't pass a value for attributes in, but remain when you do.

edited Sep 30, 2011 at 18:47

answered Sep 30, 2011 at 18:35

Conrad Frix

52.8k13 gold badges100 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Honorez Over a year ago

Many thanks, I'll look at that Monday morning (it's 9pm in Luxembourg now).

Collectives™ on Stack Overflow

Access error creating linked Oracle table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related