• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1300
  • Last Modified:

Link table using ole db

I'm using Access 2003
I have the following code which will output the table names.
I can not get the transferdatabase link line to work however.
I would like to link to the tables so i can run reports etc.
I can link to a table using Excel but not access
I will be happy with a programic or user interface way to link to the tables.
At a last resort I could import the table.
I belive my troubles have someting to do with the difference between ODBC and OLE DB.
I do not have the ODBC option with this database.



Private Sub Command0_Click()
Dim oConn As New ADODB.Connection
oConn = "Provider=ACTOLEDB.1;Data Source=C:\Documents and Settings\SERVERADMIN\My Documents\ACT_DATABASE\LectureLinx.pad;User ID=admin;Password=admin"
oConn.Open
Dim cstring As String
cstring = "Provider=ACTOLEDB.1;Data Source=C:\Documents and Settings\SERVERADMIN\My Documents\ACT_DATABASE\LectureLinx.pad;User ID=admin;Password=admin"

Set rs = oConn.OpenSchema(adSchemaTables)
Do While Not rs.EOF

Debug.Print rs("TABLE_NAME")
rs.MoveNext

Loop
DoCmd.TransferDatabase acLink, "ODBC Database", cstring, "VRP_Contact"


End Sub
0
davidpm
Asked:
davidpm
  • 5
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This link shows you how to do this:

http://www.mvps.org/access/tables/tbl0010.htm




0
 
davidpmAuthor Commented:
Whoa that's a lot of code.
I'm doubt that it will work in this instance.
What I have is a OLE DB connection.
In Excel if I do Data, Import External Data, Import Data, Connect to New data source ODC,  I can eventually see an "ACT! for Reporting Data Source" which I can open and see a table in Excel.
This is not ODBC so your ODBC code will not work.

I'm thinking that it is not possible to link a table with OLE DB but I should be able to do a query with it or copy the data to a local table.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry ... I misread somewhat.

Links are always ODBC

You can do the same thing in Access ... click File - Get External Data - Link Tables, select "ODBC Databases" in Files of Type, then build a DSN that will connect to your database ...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
davidpmAuthor Commented:
It seems as though I can not use a oledb connector to link a table. So then I thought I could use the oledb connector to create a query.
What bugs me is that the following code will display the table names in the debug windows. I AM connecting to the database.
It fails at the end and says invalid connection string for passthrough query.
Despite the fact that the exact same connection string worked for displaying the table names.
Can anyone help.

0
 
davidpmAuthor Commented:
Private Sub Command0_Click()
Dim oConn As New ADODB.Connection
Dim cString As String
cString = "Provider=ACTOLEDB.1;Data Source=C:\Documents and Settings\SERVERADMIN\My Documents\ACT_DATABASE\LectureLinx.pad;User ID=admin;Password=admin"
oConn = cString
oConn.Open


Set rs = oConn.OpenSchema(adSchemaTables)
Do While Not rs.EOF
  Debug.Print rs("TABLE_NAME")
  rs.MoveNext
Loop



Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("")
qdf.Connect = cString
strSQL = "select * from sysobjects"
qdf.SQL = strSQL

End Sub
0
 
davidpmAuthor Commented:
The following code prints out the contact names. Now how do I make this a linked table or query so I can use Access as a reporting engine for this database?

Option Compare Database

Private Sub Command0_Click()
Dim oConn As New ADODB.Connection
Dim cString As String
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
cString = "Provider=ACTOLEDB.1;Data Source=C:\Documents and Settings\SERVERADMIN\My Documents\ACT_DATABASE\LectureLinx.pad;User ID=admin;Password=admin"
oConn = cString
oConn.Open


Set rs = oConn.OpenSchema(adSchemaTables)
Do While Not rs.EOF
  Debug.Print rs("TABLE_NAME")
  rs.MoveNext
Loop

Set rs2 = New ADODB.Recordset
rs2.ActiveConnection = oConn
rs2.Source = "select * from VRP_CONTACT"
rs2.Open

Do While Not rs2.EOF
   Debug.Print rs2(25) 'This prints the names
   rs2.MoveNext
Loop

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'll be out today until this afternoon, then I'll look into this for you ...
0
 
davidpmAuthor Commented:
Another interresting point.
create a adp file in access and link it to msde.
Now when you do a file link you can get the same screens in access that you get in excel that let you pick an ole db connection.
The problem is that the ole db connection I need connects to a msde install where the vendor will not give up the sa password. ACT7 is  the product.

So at this point i cann't use the approved vendor connector olddb unless I connect to the msde server with a password the vendor will not release.
0
 
jjafferrCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    Accept: LSMConsulting {http:#16341497}

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

jjafferr
EE Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now