code_junkie

Export Oracle user tables to MS Access automaticaly

Nov 13th, 2011
191
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.78 KB | None | 0 0
  1. Sub LinkOracleTable(Server, UserID, Password, Schema, TableName)
  2.  
  3. Dim tbl As New ADOX.Table
  4. Dim cat As New ADOX.Catalog
  5.  
  6. Set cat.ActiveConnection = CurrentProject.Connection
  7.  
  8. With tbl
  9. .Name = TableName
  10.  
  11. Set .ParentCatalog = cat
  12. .Properties("Jet OLEDB:Create Link") = True
  13. .Properties("Jet OLEDB:Link Provider String") = "ODBC;" & _
  14. "Driver={Microsoft ODBC For Oracle};" & _
  15. "Server=" & Server & ";" & _
  16. "Uid=" & UserID & ";" & _
  17. "Pwd=" & Password & ";"
  18. .Properties("Jet OLEDB:Cache Link Name/Password") = True
  19. .Properties("Jet OLEDB:Remote Table Name") = Schema & "." & TableName
  20. End With
  21.  
  22. cat.Tables.Append tbl
  23. cat.ActiveConnection.Close
  24.  
  25. End Sub
  26.  
  27. LinkOracleTable "TNS Service Name", "UserName", "Password", "MySchema", "MyTable"
Add Comment
Please, Sign In to add comment