Linking Oracle Tables in MS Access using ADO through VB programmin

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nilesh Malde - 13 Mar 2007 05:50 GMT

Anybody is aware of linking oracle tables through ODBC connection in MS
Access database using ADO through VB programming.

I used docmd command but it will prompt for "select unique record
identifier" for each table. I want to be linked without promting.

I can do linking oracle table in MS access database through DAO without
prompting "select unique record identifier", it's working in windows 2000
but not working in windows xp.

Signature

NSM

Reply to this Message

Stefan Hoffmann - 13 Mar 2007 11:28 GMT

hi Nilesh,

> Anybody is aware of linking oracle tables through ODBC connection in MS
> Access database using ADO through VB programming.

It cannot done with ADO. You need ADOX.

> I can do linking oracle table in MS access database through DAO without
> prompting "select unique record identifier", it's working in windows 2000
> but not working in windows xp.

Using TableDefs works fine:

Public Function TableLinkODBC(ASourceName As String, _
Optional ADestinationName As String = "", _
Optional APrimaryKey As String = "") _
As Boolean

On Local Error GoTo LocalError

TableLinkODBC = False

ASourceName = UCase(ASourceName)
If ADestinationName = "" Then
ADestinationName = ASourceName
End If

If TableExists(ADestinationName) Then
Debug.Print "-";
CurrentDbC.TableDefs.Delete ADestinationName
End If

Debug.Print "+"; ASourceName; "="; ADestinationName
CurrentDbC.TableDefs.Append _
CurrentDbC.CreateTableDef(ADestinationName, 0, _
ASourceName, CONNECTION_ODBC)
CurrentDbC.TableDefs.Refresh

If APrimaryKey <> "" Then
SQLExecute "CREATE INDEX pk_" & ADestinationName & _
" ON " & ADestinationName & "(" & APrimaryKey & _
") WITH PRIMARY;"
End If

TableLinkODBC = True
Exit Function

LocalError:
'Error handler
End Function

CONNECTION_ODBC is a constant holding the connection string to Oracle.
CurrentDbC is a property proxy to CurrentDb.
SQLExecute encapsulates CurrentDbC.Execute.

mfG
--> stefan <--

Reply to this Message

Nilesh Malde - 19 Mar 2007 06:36 GMT

ADOX is by Microsoft. Where to get it ?

Signature

NSM

> hi Nilesh,
>
[quoted text clipped - 51 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 19 Mar 2007 11:00 GMT

hi Nilesh,

> ADOX is by Microsoft. Where to get it ?

You just need to set the appropriate reference in the VBA IDE.

mfG
--> stefan <--

Reply to this Message

Nilesh Malde - 19 Mar 2007 13:30 GMT

Thanks for your reply.

Can you tell me exact reference ? I have tried with some reference but they
didn't work. I'm not getting methods of CurrentDBC.

Signature

NSM

> hi Nilesh,
>
[quoted text clipped - 3 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 19 Mar 2007 14:19 GMT

hi Nilesh,

> Can you tell me exact reference ? I have tried with some reference but they
> didn't work.

You need to reference the Microsoft ADO Extensions in the msadox.dll.

I'm not getting methods of CurrentDBC.
This is a proxy function to increase the speed when accessing CurrentDb.
Place the following code in a standard module:

Private m_CurrentDb As DAO.Database

Public Property Get CurrentDbC() As DAO.Database

If m_CurrentDb Is Nothing Then
Set m_CurrentDb = CurrentDb
End If
Set CurrentDbC = m_CurrentDb

End Property

mfG
--> stefan <--

Reply to this Message

Nilesh Malde - 20 Mar 2007 06:15 GMT

Dear Stefan Hoffmann,

Lots n lots of thanks for your help.

Now it's works.

BTW TableExists is independant procedure or linked with ADOX.

Thanks again.

Signature

NSM

> hi Nilesh,
>
[quoted text clipped - 19 lines]
> mfG
> --> stefan <--

Reply to this Message