MS access to SQL Server

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.
TarasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
Is there some particular reason that you don't want to use a linked table and a bound form?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TarasAuthor Commented:
Thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.