August 29, 2008 03:00pm pdt

Member Login

Hall of Fame

Yearly

1angelIII 57,177
2harfang 31,630
3capricorn1 31,160
4Dhaest 24,893
5LSMCons... 24,156
6RobSamp... 20,800
7DatabaseMX 19,100
8jpaulino 18,850
9peter57r 18,229
10jimhorn 16,378
11ee_rlee 15,865
12hielo 15,700
13rorya 15,645
14emoreau 14,500
15frankytee 13,800
16aiklamha 13,200
17boag2000 13,000
18Arthur_... 12,800
19danaseaman 11,250
20MikeToole 11,000
21mlmcc 10,752
22zorvek 10,100
23bigjokey 10,000
24TimCottee 9,904
25saurabh726 9,900

	[x] Attachment Details

Trouble getting connected to an Access db that has an ODBC linked table

Zone: VB Database Programming

Tags: VB

I have simplified the problem to this: I have an Access database (C:\test.mdb) that has two tables. One is linked to a table in Northwind.mdb. The other is linked to a table in a database through an ODBC connection.

The link to Northwind works fine. I have some syntax issue with the ODBC connection.

When I linked the table in Access I first had to set up a "Machine Data Source". Then I had to provide a Data Source Name and a "Login ID". There is no password to the database. I'm sure this is the part that I am messing up.

I have attached my code and the database. Of course the links to the tables will not work.

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

           
           
             Private Sub Form_Load()
    Dim dbLinked As ADODB.Connection
    Dim rsLinked As ADODB.Recordset
    Dim sLinked As String
 
    Dim dbODBCLinked As ADODB.Connection
    Dim rsODBCLinked As ADODB.Recordset
    Dim sODBCLinked As String
 
'Get LinkID.
    Set dbLinked = New ADODB.Connection
    Set rsLinked = New ADODB.Recordset
    dbLinked.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb"
    sLinked = "SELECT LinkedData.ProductName From LinkedData ORDER BY LinkedData.ProductName;"
    rsLinked.Open (sLinked), dbLinked
    
    Debug.Print "Linked:  "; rsLinked!ProductName
    
'Get ODBCImportedID.
    Set dbODBCLinked = New ADODB.Connection
    Set rsODBCLinked = New ADODB.Recordset
    dbODBCLinked.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.mdb"
    sODBCLinked = "SELECT ODBClinkedData.ProductName From ODBClinkedData ORDER BY ODBClinkedData.ProductName;"
'The next line is the one giving me trouble.  The error is "No value given for one or more required parameters."
    rsODBCLinked.Open (sODBCLinked), dbODBCLinked, , , False
    
    Debug.Print "ODBCImported:  "; rsODBCLinked!ProductName
    
End Sub
           
         

Attachments:

Test.mdb (288 KB) (File Type Details)

The database in question

Start your free trial to view this solution

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

8.1

Question Stats

Zone: Programming

Question Asked By: randys106

Solution Provided By: TimCottee

Participating Experts: 1

Solution Grade: B

Translate:

Loading Advertisement...

20080723-EE-VQP-34 / EE_QW_2_20070628