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

Dan,

Try creating a view in SQLS and link to it via the ODBC wizard
or create a .udl file and link that way. Then use an append query.

-
Michael R Mattys
MapPoint and Database Dev
www.mattysconsulting.com
-
----- Original Message ----- 
From: "Dan Waters" <dwaters at usinternet.com>
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Sent: Sunday, December 07, 2008 7:39 PM
Subject: Re: [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
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com