Select Data from SQL into an Access table

[MichaelC]

Hi all -
I'm hoping someone can help point me in the right direction. I've spent a lot of time researching this on the internet but nothing I've found is working for exactly what I want to do.

I want to use Access 2003 to do some reporting on a SQL 2005 database located on the network. Access should never modify the SQL database, only pull data from it to work with. I've successfully connected to the database and can copy ALL records from a SQL table into Access. My problem is I can't seem to figure out how to select only certain records to pull into Access. Here's basically the code I'm using so far:

dim rs as ADODB.recordset
dim conn as ADODB.Connection


dim db as Database
dim dbf as Database
dim mycommand as ADODB.Command

set conn = new ADODB.connection
set db=currentdb
set dbf = currentdb

conn.provider="SQLOLEDB.1"
comm.open "connectionstring"

set mycommand = new ADODB.Command
mycommand.activeconnection=conn
mycommand.commandtext = "select field from table"
set rs=new ADODB.recordset
rs.open "table", conn, adOpenDynamic, adLockPessimistic
rs2=dbf.openrecordset("table")
rs.movefirst
do until rs.eof
***copy record to Access, movenext then close everything.

I'm sure this is very sloppy - sorry, trying to learn without any good reference. This works for copying ALL records, but I don't want this. How can I use a SQL select statement to choose only the records that fit my criteria. I understand how to write the select statement, just don't know how to implement it or where the records go after the select statement is used. Any advice is greatly appreciated. Thank you!

[pbaldy]

The direct answer to your question:

rs.open "SELECT * FROM table WHERE FieldName = Whatever", conn, adOpenDynamic, adLockPessimistic

Is there a reason you copy the records into Access rather than just querying the SQL Server data? I'd use a linked table and just run my queries directly on the SQL Server data.

[MichaelC]

There's no reason other than I really am not sure how to use any of this from Access. How would I link a table - that actually sounds more efficient. Also, after I link the table and run a SQL query, where exactly is the data so that I can work with it?

Now that I think about this, a linked table might not work. I need to perform some update queries on the data. For example the data in SQL may contain code 123, but I need to update that so the reports reflect a different, more user recognizable name. Again, I cannot modify SQL from Access at all. It's in our contract that we will not modify data in the tables using any software other than what came with it.

[MichaelC]

Paul -
Thank you so much for the help. That simple statement made all the difference. Everything is clicking in my brain now haha. I was really confused with the whole "mycommand" thing that I was trying.

[pbaldy]

No problemo. I might still go with a linked table. From what you describe, I might have a lookup table that could be joined in a query to the main table, which would let you get the equivalent of 123. That would have to be built, or might actually already exist in SQL Server.