TransferDatabse aclink probem linking to database

[vzjfgm]

I am linking to a Oracle database via VB code. I am using DoCmd.TransferDatabaseLink command to link to 6 tables in a data base. Some of these tables have indexes and some do not. On the tables that do not have indexes a dialog pops up to select unique record identifiers. This dialog is giving me problems. I could use a sendkey command to hit enter bypass the dialog and to link the next table. The problem arrises when I do not know which table may need the sendkeys command. I want the users to have the abiltiy to add more tables to link. How do I know when to use the send key command if the dialog is displayed. Thanks in advance for any help!!!

 

[BillPower]

Hi vzjfgm,

I seem to remember a year or two ago having the same problem. I'm sure I used:

DoCmd.SetWarnings False
'DoCmd.TransferDatabase , acLink etc
DoCmd.SetWarnings true

to suppress the warning messages, Access still seemed to recognize the indexed fields. Can't check because I don't have Oracle on my PC.

Bill

 

[vzjfgm]

Thanks for your suggestion. I already have that code in my program. The problem I am having is that some of the tables do not have an indexed field and a dialog box pops up for the user to select an indexed field. Is there any way I can find out if there is a dialog box present?

 

[BillPower]

Hi vzjfgm,

Sorry for the delay in responding, I wasn't able to find a way to suppress the dialog box using the TransferDatabase method. This code does exactly the same function, links your tables to an Oracle DB using ODBC, but you shouldn't get a dialog box:

Dim dbs As Database, tdf As DAO.TableDef
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef(" YourTableName&quot
tdf.Connect = "ODBC;DRIVER={Microsoft ODBC for Oracle};SERVER=YourServer;UID=BillP;PWD=1234"
tdf.SourceTableName = "dbo.YourTableName"
dbs.TableDefs.Append tdf

You will need to have the Miscrosoft DAO 3.x Object Library installed in your references.

Just in case, to do this, open the Visual Basic Editor, from the menu select, Tools>> References, if the above library isn't checked, scroll down the list and check the highest version no. to install it.

Edit the red items above to reflect your own table, password etc.

Bill

 

[vzjfgm]

Hi Bill,
I tried the following code and it gives me a run time
error on the append line of code. Any Suggestions?

Thank you!!!
Karen

Error: could not find the object 'dbo.TPD_EQUIEQUIPMENT'.
Make sure object exists and that you spell its name Correctly


Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("TPD30_EQUIPMENT&quot
tdf.Connect= "ODBC;DATABASE=PTPD;UID=xxxx;PWD=xxxx;DSN=PTPD"
tdf.SourceTableName = "dbo.TPD30_EQUIPMENT"
dbs.TableDefs.Append tdf

 

[BillPower]

Link "TPD30_EQUIPMENT" manually and then put this code in the On Click event of a button. While still in the Visual Basic Editor open the Immediate Window by pressing Ctrl+G at the same.

Still with the VBE open, open the form with the new button with this code behind it in normal view. Click the button. Go back to the VBE, you should see the "Connection String" to the Oracle DB and the Table Name that needs to be referred to as the "SourceTableName" in the Immediate Window.

Dim dbs As DAO.Database, tdf As DAO.TableDef
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If tdf.Attributes And dbAttachedODBC Then
If Len(tdf.Connect) > 0 Then
Debug.Print tdf.Connect
Debug.Print tdf.SourceTableName
End If
End If
Next tdf
Set dbs = Nothing

Bill