Solved

ADO connection

Posted on 2002-04-30
5
479 Views
Last Modified: 2006-11-17
This is the first time I am using ADO. Could someone give some sample code to connect to a SQL database? I have a linked table to the sql db and a query to lookup some information from that table. I have done this using DAO (see below), but the sql database is asking for username and password that I do not want the users to see.
Thanks in advance,
Jim


Dim medrec As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("qry lookup", dbOpenSnapshot, dbReadOnly)
0
Comment
Question by:jmrasmus
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
KMAN earned 100 total points
ID: 6981708
Here is sample code to connect using ADO to any ODBC datasource.

Dim myCn As New ADODB.Connection
Dim myRs As New ADODB.Recordset
   
myCn.ConnectionString = "DSN=myDataSource"
myCn.Open
myRs.Open "SELECT * FROM myTable", myCn, adOpenDynamic

Good Luck!

K
0
 
LVL 5

Expert Comment

by:KMAN
ID: 6981727
Don't forget to add "Microsoft ActiveX Data Objects..." reference to your app.
0
 

Author Comment

by:jmrasmus
ID: 6981798
Kman,
does the myrs recordset have the same properties as the DAO recordset? By that I mean using DAO I can loop through the rescordset, do compairisons to a particular field ie myrs.Fields("Last Name").value="Jones".
Thanks
Jim
0
 
LVL 5

Expert Comment

by:KMAN
ID: 6981825
Very similar to DAO and many of the same properties are available, and some new ones added.

K
0
 

Author Comment

by:jmrasmus
ID: 6982081
Thanks loads!!!!!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
View vs Edit mode enhancements 12 40
Adding New Records into SQL Server Table from MS Access 4 30
DBF to ... Converter 5 43
Track name AutoCorrect info 14 47
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now