Solved

Want to connect Access 2007 to SQL Server 2008

Posted on 2011-09-09
3
276 Views
Last Modified: 2012-05-12
Hi Everybody,

I am trying to connect my Access 2007 database to a SQL Server  2008 database. I have tried the following string that I found, but it is not working.
---------------------------------------------------------------------------------------------------
Private Sub Command0_Click()

Dim MyConnObj As New ADODB.Connection    <--- Problem highlighted in yellow here
Dim myRecSet As New ADODB.Recordset
Dim sqlStr As String

MyConnObj.Open _
 "Provider = sqloledb;" & _
 "Data Source=timvmsql1;" & _
 "Initial Catalog=TESTWORLD;"


   sqlStr = "select * from contacts"

   myRecSet.Open sqlStr, MyConnObj, adOpenKeyset

   MsgBox "Total Number of records = " & myRecSet.RecordCount


End Sub
---------------------------------------------------------------------------------------------

It does not seem to recognize the ADODB functionality. Is there somewhere that I need to enable this functionality or is there another method?  Perhaps DAO?

Thanks,
0
Comment
Question by:jonsuns7
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 36514349
Do you have the ADO library loaded as a reference in your VBE?

Tools-->References-->
0
 

Author Closing Comment

by:jonsuns7
ID: 36514969
I had figured out the answer, that was the same as you mentioned. But since nobody else answered, I'll give you the points. Have a good weekend.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36515010
ok
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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