copy records from 1 table to another

[Zygor]

I am trying to copy a range of records from an Acess database on a server to an SQL server table.

I have a wealth of experience as I've made 1 stored procedure already. I feel I may be close but could use a nudge. Here's what I have so far. It is a create procedure but I'll execute it after I create it.

CREATE PROCEDURE UpdateTable
@StartDate DATETIME,
@EndDate DATETIME
AS
INSERT INTO TABLE2 SELECT * FROM TABLE1
WHERE ActPatInDate BETWEEN @StartDate AND @EndDate;

I intend to pass the criteria when I execute it in VBA. TABLE2 is my sql server table. It is called 'Results'.

TABLE1 is a table called tbleCombined from my Access database on a totally different server, but one which I am mapped to.

Any help would be greatly appreciated. Thank you.

 

[bborissov]

Check:
Accessing External Data
OPENROWSET() function
Linked servers

All in BOL. The easiest way is to add a linked server to your Access DB and then:

CREATE PROCEDURE UpdateTable
       @StartDate DATETIME,
       @EndDate DATETIME
AS
INSERT INTO TABLE2
SELECT * 
       FROM LinkedServerName..TABLE1
       WHERE ActPatInDate BETWEEN @StartDate AND @EndDate

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[genomon]

I intend to pass the criteria when I execute it in VBA.

Why not link the table into Access and save the bother of writing a stored proc?

Ever notice how fast Windows runs? Me neither.