473,499 Members | 1,610 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

sql server pass through query

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
2 6333
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
16868
by: polytimi8 | last post by:
Hello, I would like to know if it is possible to create a form in Access2000, which would function like a calendar for 8 operating rooms in hospital, showing which hours are those closed for a...
6
6749
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
7
6388
by: rickcheney | last post by:
I just changed my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was clicked a Report was...
3
9245
by: dk | last post by:
Hi all, Would appreciate some advice on the following: I am trying to speed up an Access database connected to a SQL Server back-end. I know I can use a pass-through query to pass the sql...
1
2389
by: JM | last post by:
Hello, Using Access 2000 queries, you can reference(pass) form values directly using syntax like Forms!frmPaint!txtColor. I want to do a pass through query to SQL Server 2000, but I don't know...
3
1772
by: Maciek Zywno | last post by:
Hi, I managed to do this by using a query and !! syntax, but it does not work with SPs. So I created a procedure with "@" to define a paraemter, but how can I pass a value to this procedure from...
15
7213
by: brettclare | last post by:
I have linked a large SQL Server table to Access, however 'only' 2,195,439 records are shown and are available to query. Can I increase the size (cache??)/number of records showing in Access? ...
2
4414
by: ILCSP | last post by:
Hello, I'm in the process of changing our 'normal' Access 2000 update queries to Update Pass Through Queries. We have a SQL server 2000 database and we're using an Access 2000 database as our...
20
37830
by: TC | last post by:
I need an automated procedure to copy data from an Access table to a SQL Server table. Speed is important. What is the recommended technique? I can export the data from Access, copy it via FTP,...
5
6302
by: marshmallowww | last post by:
I have an Access 2000 mde application which uses ADO and pass through queries to communicate with SQL Server 7, 2000 or 2005. Some of my customers, especially those with SQL Server 2005, have had...
0
7132
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
7009
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
1
6899
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
5475
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
4602
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
0
3103
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
0
3094
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
665
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
302
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.