Mike Mattys
mmattys at rochester.rr.com
Sun Dec 7 19:37:47 CST 2008
Here is an example for oledb using a udl file http://www.blueclaw-db.com/website_database_connections/active_server_pages_datalink.htm There are many such on the net - 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 8:20 PM Subject: Re: [AccessD] Move Data from SQL Table to Access FE Table > Thanks Mike, but I can't use ODBC at all. > > Dan > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mike Mattys > Sent: Sunday, December 07, 2008 6:48 PM > To: Access Developers discussion and problem solving > Subject: Re: [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 > > -- > 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