Microsoft Access to SQL Server

Last Post 01 Oct 2004 09:35 PM by VladRUS. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
acfalcon
New Member
New Member

--
08 Aug 2004 04:31 PM
I am trying to insert data from a Microsoft Access Database into a SQL Database in a Stored Procedure that has Parameters. I have never written a query in SQL Server to get data from Microsoft Access. Can someone please give my a location where I can find sample code to do this?

Thanks for the help,
Adam
VladRUS
New Member
New Member

--
01 Oct 2004 09:35 PM
Step-By-Step Sample:

1. SQL Server - Query Analyzer
if object_id('tInsertFromAccess') > 0
drop table tInsertFromAccess
go

create table tInsertFromAccess(id int identity primary key, TestData varchar(36))
go

if object_id('spi_tInsertFromAccess') > 0
drop procedure spi_tInsertFromAccess
go

create procedure spi_tInsertFromAccess (@TestData nvarchar(36))
as

insert into tInsertFromAccess(TestData)
select @TestData
go

-- test
declare @TestData nvarchar(36)
set @TestData = NewID()
exec spi_tInsertFromAccess @TestData = @TestData
go

select * from tInsertFromAccess

2. ODBC
- Create ODBC Data Source to SQL Server

3. MS Access
- Create New Query in Design View
- Click SQL View
- Copy and Paste Query Text:

declare @TestData nvarchar(36)
set @TestData = NewID()
exec spi_tInsertFromAccess @TestData = @TestData


- Click Query>SQL Specific>Pass-Through
- Click Property
- In ODBC Connect Str>...> select just created ODBC Data Source
- In Returns Records set No
- Run Query

Enjoy
BobBarker
New Member
New Member

--
22 Dec 2004 10:50 AM
Personally, I think you'd be nuts to try to pull the data from Access. You should push it from Access to SQL Server. Otherwise you'd have to install Access on your SQL Server machine.

The easiest thing to do would be to copy the entire Access database to SQL Server, then write T-SQL code to iterate through the table and call the stored procedure. You can copy data from Access to SQL fairly easily by using DTS, or Access itself (via linked tables).

BB



Acceptable Use Policy
---