[AccessD] Move Data from SQL Table to Access FE Table

Hi Bobby,

When connecting an Access FE and SQL BE using an OLEDB provider, the SQL
statement can't refer to a table from both FE and BE at the same time.
There is not table link for Access to recognize.  If I was connecting with
SQL table links, this would have worked fine.

Thanks - Dan

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
Sent: Sunday, December 07, 2008 6:23 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Move Data from SQL Table to Access FE Table

Dan,

What about something like (air code and assumes LocalIDTable and SQLTable
both have a field called ID):

Dim db as database
Dim sql as string

sql = "INSERT INTO LocalIDTable " & _
	   "SELECT ID " & _
		"From SQLTable"

Set db=currentdb()
Db.execute sql,dbfailonerror
Db.close
Set db=nothing


If the above sql statement isn't exactly what you need it should point you
in the right direction.

Bobby


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Sunday, December 07, 2008 6:58 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Move Data from SQL Table to Access FE Table

I'm learning to use SQL as a BE for Access apps.  The connection method is
OLEDB rather than ODBC tables.

I would like to copy a list of record ID's from a table in the SQL BE to a
local FE table each time a process screen is opened.  I'm doing this by
opening a view from Access, creating an ADODB recordset, and then looping
through that recordset to insert the ID numbers into a local FE table.

But, looping to do this seems quite inefficient for hundreds or thousands of
rows. 

Is there a better way?

Thanks!
Dan




-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com