Access to SQL

[Dataman]

I have a query in access 2003 containing new patient records that needs to
be inserted into a SQL server 2005 table. The code below is run from the
Access database. I do NOT want to use linked tables or pass thriugh queries.

ImportData contains the connect string. The Access front end will reside on
the local computer NOT hosting the SQL server.

The qryUniqueNewPatients query is on the access database side. The FROM
clause is looking for something from the SQL server side. I need it to look
at the qryUniqueNewPatients query on the Access side.

This has me stuck. Any help is appreciated.
============================================
Set cnn = New ADODB.Connection

cnn.Open ImportData

strSQL = "INSERT INTO dbo.tblPatients (PFirstName, PLastName,
MedicalRecNumber,DOB,MRSNID,EmployeeID) "

strSQL = strSQL & "SELECT PFirstName, PLastName,
MedicalRecNumber,DOB,MRSNID,EmployeeID "

strSQL = strSQL & "FROM qryUniqueNewPatients"

cnn.Execute strSQL
==============================================


Kurt

 

[Banana]

I do NOT want to use linked tables or pass thriugh queries.
Why?

The qryUniqueNewPatients query is on the access database side. The FROM
clause is looking for something from the SQL server side. I need it to look
at the qryUniqueNewPatients query on the Access side.

That would probably require SQL Server to link back to the Access
database, which isn't necessarily a practical solution here, and
performance may not be that good. By default, SQL Server has no
knowledge about what tables Access has, though JET/ACE (the database
engine of Access) is much more flexible and can work with SQL Server via
linked tables and thus perform a heterogeneous operation (e.g. joining
an Access table to a SQL Server table).

Since you are wanting a heterogeneous operation, I see you having two
alternatives, depending on the amount of data you need to filter through.

1) Use a linked table and have a local query join the table and perform
a heterogeneous operation.

2) Wrap the data into an array and send it off to SQL Server.

Neither are that great in terms of performance because in both cases you
must process both SQL statement & data to determine the final resultset
though I probably would prefer #2 to #1 if the data or processing the
filter was massive task.

An alternative, depending on how often the data get updated, is to
maintain a copy of table containing all distinct values from the other
source and join against that table instead of doing a heterogeneous
operation, and it's simple to do a 'truncate-and-reload' if you need to
update the data.


Maybe if you explain a bit more about the process and whys we can come
up with a better solution.

 

[Dataman]

OK then, I will use linked tables.

What I need now is some vba code to refresh the links???

Thanks

Kurt

 

[Daniel Pineault]

The code to relink tables can be found at
http://www.mvps.org/access/tables/tbl0009.htm
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Dataman]

The code mentioned puts the table name in front of the link string and chops
off the first few characters.

this is MY connect string that I use. "Provider=SQLOLEDB;data
Source=My-SERVER;Initial Catalog=MyDatabase;User Id=xyz;Password=xyz"
for all non linked activity.

Somehow, I think this is a big part of the linking problem.

Kurt