Linking Oracle Tables in MS Access using ADO through VB programmin

[Nilesh Malde]

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.

 

[Stefan Hoffmann]

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 <--

 

[Nilesh Malde]

ADOX is by Microsoft. Where to get it ?
--
NSM

hi Nilesh,


It cannot done with ADO. You need ADOX.

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 <--

 

[Stefan Hoffmann]

hi Nilesh,

ADOX is by Microsoft. Where to get it ?

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


mfG
--> stefan <--

 

[Nilesh Malde]

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.

 

[Stefan Hoffmann]

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 <--

 

[Nilesh Malde]

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.