?
Solved

Link table using ole db

Posted on 2006-03-30
10
Medium Priority
?
1,284 Views
Last Modified: 2007-12-19
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
Comment
Question by:davidpm
  • 5
  • 3
9 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 16341497
This link shows you how to do this:

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




0
 

Author Comment

by:davidpm
ID: 16342627
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
 
LVL 85
ID: 16345352
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:davidpm
ID: 16348466
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
 

Author Comment

by:davidpm
ID: 16348493
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
 

Author Comment

by:davidpm
ID: 16348532
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
 
LVL 85
ID: 16349147
I'll be out today until this afternoon, then I'll look into this for you ...
0
 

Author Comment

by:davidpm
ID: 16367333
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 16822838
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question