By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,661 Members | 1,319 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,661 IT Pros & Developers. It's quick & easy.

sql server pass through query

P: n/a
Greetings,

I was wondering if anyone could help me with a project involving MS
Acces as a front-end to an SQL Server 2000 database.
I am running a program that currently populates the Access database,
but I would like to be able to send this data directly to the SQL
Server also.
I am currently using a DSN connection, but would prefer to use
ADO/ADOX. I'm thinking that I need to use a pass-through Update query
to update an identical database schema on the SQL Server, but I'm
running into troubles and I'm not very familiar with SQL Server,
stored procedures, pass-through queries and the other options that
seem to be potential soulutions. Any help and /or code snippets would
be appreciated.

Daryl
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On 10 Feb 2004 10:08:15 -0800, da************@netscape.net (djharrison) wrote:
Greetings,

I was wondering if anyone could help me with a project involving MS
Acces as a front-end to an SQL Server 2000 database.
I am running a program that currently populates the Access database,
but I would like to be able to send this data directly to the SQL
Server also.
I am currently using a DSN connection, but would prefer to use
ADO/ADOX. I'm thinking that I need to use a pass-through Update query
to update an identical database schema on the SQL Server, but I'm
running into troubles and I'm not very familiar with SQL Server,
stored procedures, pass-through queries and the other options that
seem to be potential soulutions. Any help and /or code snippets would
be appreciated.

Daryl


As I see it, you have 2 decent options, neither of which involves using Access
and ADO.

1. Use Access and DAO. Link to the data you want to import, and use insert
queries to copy data to the server. This approach is very flexible, and
reasonably fast. If you try to use ADO, you'll lose the ability to do
heterogeneous joins - unless you're using the ADO provider for JET and still
linking the tables, but then why bother with ADO?

2. Use Transact SQL on the server side to query the data directly from SQL
Server via an ADO driver. This technique has limited flexibility, but is
blazing fast!
Nov 12 '05 #2

P: n/a
Here is an example where you read data from a stored procedure in Sql
Server using com based ADO (regular ADO). Say the stored procedure in
Sql Server looks like this:

Create Procedure [stp_Tbl1Data]
@startDate datetime,
@endDate datetime

As

Select * from tbl1 Where Datefld Between @startDate and @endDate
Go

This procedure's name is stp_Tbl1Data and has 2 parameters -- @startDate
and @endDate. Here is how you call it from Access with the parameters:

Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, i As Integer

cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source =
SqlServer1;Initial Catalog=Database1;UID=sa;PWD='"
cmd.CommandTimeout = 600 'seconds - 10 minutes
cmd.CommandType = adCmdStoredProc
cmd.CommanText = "stp_Tbl1Data"
cmd.Parameters("@startDate").Value = #1/1/04#
cmd.Parameters("@endDate").Value = #1/31/04#
Set RSado = cmd.Execute
Set RSdao = DB.OpenRecordset("AccessTbl1")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 to RSdao.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
Loop
RSdao.Close
RSado.Close
cmd.ActiveConnection.Close

This will retrieve thousands of records from Sql Server in a matter of
seconds. The flexibility of a routine like this is relative to your
proficiency with Sql Server and ADO. The only thing faster than this
would be ADO.Net where you don't even have to use a loop to stuff the
data into Access - just use an Insert statement on a data Adapter (sorry
for the jargon -- just FYI).

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.