Found out how to do this after another trawl through BOL. I can achieve what I need to do by using OPENDATASOURCE, but as far as I can tell I have to have a table to insert the date into, i.e. this doesn't run
select field1, field2
into OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0','Data Source="\\server\sharename\ExportData\Test.mdb";User ID=Admin;Password=; ')...Results
FROM dbo.tblwwAvailByAgentTown
thus I have to empty the Access table, and use
insert into OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="\\server\sharename\ExportData\Test.mdb";User ID=Admin;Password=; ')...Results
(field1, field2)
select field1, field2
FROM dbo.tblwwAvailByAgentTown
Not the simplest solution, but its workable.
cheers
paddy
----------------------------
Paddy McKay
Technical Manager, Scotland
Scottish Property Network Limited
T: +44 (0)141 561 7300
F: +44 (0)141 561 7319
www.scottishproperty.co.uk
| [EMAIL PROTECTED]
22/09/2004 16:36
|
To: [EMAIL PROTECTED] cc: Subject: [ cf-dev ] Exporting to Access from SQL Server in CF |
Hi all,
I want to export the results of a query on an MSSQL database into Access.
I know how to link Access to another Access DB in CFQuery,and the same for two SQL Servers, but how do you structure a query to export from SQL Server into Access?
I tried the following based on the Access to Access logic I have used before, but it doesn't work.
<CFQUERY NAME="Export1" DATASOURCE="dsn">
SELECT AgentName, ARN, TypeName, TypeCode, TotalUnits, FloorSpace, PropertyAgeBandLabel, PropertyAgeBand, SizeBandLabel, SizeBand, PropertyTypeLabel, PropertyType, month, year, statsdate, created, spndmid
into \\server\sharename\ExportData\Test.mdb.tblwwAvailByAgentTown
FROM dbo.tblwwAvailByAgentTown
</CFQUERY>
Any ideas?
TIA
paddy
----------------------------
Paddy McKay
Scottish Property Network Limited
T: +44 (0)141 561 7300
F: +44 (0)141 561 7319
www.scottishproperty.co.uk
| This email is from Scottish Property Network Limited. The E-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Any unauthorised dissemination or copying of this e-mail, and any use or disclosure of any attachments contained in it, is strictly prohibited and may be illegal. If you have received the E-mail in error please notify us on [EMAIL PROTECTED] or telephone 0141 5617300. Any views expressed by an individual within this E-mail do not necessarily reflect the views of the firm. Our general contact details are; Scottish Property Network Limited 26 New Street Paisley PA1 1YB Telephone 0141 561 7300 Fax 0141 561 7319 |
| This email is from Scottish Property Network Limited. The E-mail and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Any unauthorised dissemination or copying of this e-mail, and any use or disclosure of any attachments contained in it, is strictly prohibited and may be illegal. If you have received the E-mail in error please notify us on [EMAIL PROTECTED] or telephone 0141 5617300. Any views expressed by an individual within this E-mail do not necessarily reflect the views of the firm. Our general contact details are; Scottish Property Network Limited 26 New Street Paisley PA1 1YB Telephone 0141 561 7300 Fax 0141 561 7319 |
