Solved

MS access to SQL Server

Posted on 2014-03-31
3
532 Views
Last Modified: 2014-04-02
I need to connect my Access 2010 form with SQL Server 2012 table ”tblMaster”  seating in SQL Server database named “Production_db” .
I would like to use ADO to connect and pull out some records from table” tblMaster”.
For that reason I probably need to initiate reference Microsoft Active X Data Object control  2.8 in MS Access reference settings
I would like to have command button on form which I click and make connection to table in SQL Server database  then select  particular record  from table and then display it on form fields.
 My PC is on company network.
SQL Server database is seating on one company server named Prod_Serv  that is on company network too.
I need code that will be behind this command button.
0
Comment
Question by:Taras
3 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39967886
Is there some particular reason that you don't want to use a linked table and a bound form?
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39968921
If you can't use linked tables, then you must do this:

1. Create an ADO or DAO connection. Since you're dealing with server-based data, ADO would be the better choice, and as you indicate you would need to set a reference to the ADO library:

Dim con As New ADODB.Connection
con.ConnectionString = "Your Connection String"
con.Open

Here's a good resource for connection strings: http://www.connectionstrings.com/sql-server/

2. Create a recordset using that connection:

Dim rst As New ADODB.Recordset
rst.Open "SELECT * FROM SomeTable WHERE IDField=123", con

You should then have the data from "SomeTable" in that recordset, for the defined criteria. You could then use one of several methods to show that data. If it's just a few fields, you could do this:

Me.Textbox1 = rs("Field1")
Me.Textbox2 = rs("Field2")
and so on

If there are a LOT, then you can (a) create textboxs with EXACTLY the same name as the recordset fields and then (b) loop through the Fields collection of the recordset and set the value:

Dim i as Integer
For i = 0 to rs.Fields.Count - 1
  Me.Controls(rs.Fields(i).Name) = rs.fields(i)
Next i
0
 

Author Closing Comment

by:Taras
ID: 39972132
Thanks a lot
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

911 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

20 Experts available now in Live!

Get 1:1 Help Now