Export SQL Table to MS Access file

I want to export a SQL table to new MS Access file or to a file already
existing in a known location. The SQL table is a temporary table (#). If not
for # table, any idea to export a SQL table will be useful. The exporting
logic should be coded in stored procedure.
* I don't want to use the option that uses Access file as Linked Server.
* If it is required to have the SQL table structure in MS Access file,
that's fine.Evening!
Here is an example of how to issue a SELECT statement against an Microsoft
Access database table in the SQL Query Analyzer or a stored proc.
SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\test.mdb';'admin';'', Orders) AS a
Note the path of the access database and the name of the table called
Orders. You can change that to be the path and table that matches what
you've called it.
Now here is a quick example on how to insert data into the access table
orders from a sql query. Again, you can run this via the analyzer or stored
proc.
INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'',
Orders)
SELECT * FROM #temp_orders
Basically, think of the "OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\test.mdb';'admin';'', Orders)" as a table. You can issue an INSERT,
UPDATE, DELETE just like you would any table.
Hope this helps,
Yosh
"Jafer" <Jafer@.discussions.microsoft.com> wrote in message
news:1836ADD0-217A-41C2-A0C7-2E9AA440E65C@.microsoft.com...
>I want to export a SQL table to new MS Access file or to a file already
> existing in a known location. The SQL table is a temporary table (#). If
> not
> for # table, any idea to export a SQL table will be useful. The exporting
> logic should be coded in stored procedure.
> * I don't want to use the option that uses Access file as Linked Server.
> * If it is required to have the SQL table structure in MS Access file,
> that's fine.|||Hi
If you are wanting to automate this then you may want to look at using DTS
and a scheduled job.
John
"Jafer" <Jafer@.discussions.microsoft.com> wrote in message
news:1836ADD0-217A-41C2-A0C7-2E9AA440E65C@.microsoft.com...
>I want to export a SQL table to new MS Access file or to a file already
> existing in a known location. The SQL table is a temporary table (#). If
> not
> for # table, any idea to export a SQL table will be useful. The exporting
> logic should be coded in stored procedure.
> * I don't want to use the option that uses Access file as Linked Server.
> * If it is required to have the SQL table structure in MS Access file,
> that's fine.|||Great. Thanks. Is there a way in which I need not have the table structure i
n
Access database?
"Yosh" wrote:

> Evening!
> Here is an example of how to issue a SELECT statement against an Microsoft
> Access database table in the SQL Query Analyzer or a stored proc.
> SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders) AS a
> Note the path of the access database and the name of the table called
> Orders. You can change that to be the path and table that matches what
> you've called it.
>
> Now here is a quick example on how to insert data into the access table
> orders from a sql query. Again, you can run this via the analyzer or store
d
> proc.
> INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'',
> Orders)
> SELECT * FROM #temp_orders
>
> Basically, think of the "OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders)" as a table. You can issue an INSERT,
> UPDATE, DELETE just like you would any table.
> Hope this helps,
> Yosh
>
>
>
>
> "Jafer" <Jafer@.discussions.microsoft.com> wrote in message
> news:1836ADD0-217A-41C2-A0C7-2E9AA440E65C@.microsoft.com...
>
>|||Yosh,
fyi... I don't have permissions to create linked server. When I tried to
execute your code, I'm getting the below error
Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied.
You must access this provider through a linked server.
Please advise.
"Yosh" wrote:

> Evening!
> Here is an example of how to issue a SELECT statement against an Microsoft
> Access database table in the SQL Query Analyzer or a stored proc.
> SELECT a.* FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders) AS a
> Note the path of the access database and the name of the table called
> Orders. You can change that to be the path and table that matches what
> you've called it.
>
> Now here is a quick example on how to insert data into the access table
> orders from a sql query. Again, you can run this via the analyzer or store
d
> proc.
> INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'',
> Orders)
> SELECT * FROM #temp_orders
>
> Basically, think of the "OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders)" as a table. You can issue an INSERT,
> UPDATE, DELETE just like you would any table.
> Hope this helps,
> Yosh
>
>
>
>
> "Jafer" <Jafer@.discussions.microsoft.com> wrote in message
> news:1836ADD0-217A-41C2-A0C7-2E9AA440E65C@.microsoft.com...
>
>|||Jafer,
Treat the "OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';'',
Orders) As a" as you would any table in SQL.
For example, this will work:
SELECT * INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\test.mdb';'admin';'', Orders) FROM #temp_orders.
You notice the 'admin' password? Try playing with that if you have
permission problems. Try making it blank or leaving it out completely.
Hope this helps,
Yosh
"Jafer" <Jafer@.discussions.microsoft.com> wrote in message
news:F408FF70-8F6A-47A5-88A4-9C001A3AE7C5@.microsoft.com...
> Great. Thanks. Is there a way in which I need not have the table structure
> in
> Access database?
> "Yosh" wrote:
>|||Yosh,
SELECT * INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\test.mdb';'admin';'', Orders) FROM #temp_orders.
gives, incorrect syntax error near the key word OPENROWSET. I gave the exact
location of the MDB file along with the table name in the file.
Any thoughts?
"Yosh" wrote:

> Jafer,
> Treat the "OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\test.mdb';'admin';''
,
> Orders) As a" as you would any table in SQL.
> For example, this will work:
> SELECT * INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders) FROM #temp_orders.
> You notice the 'admin' password? Try playing with that if you have
> permission problems. Try making it blank or leaving it out completely.
> Hope this helps,
> Yosh
>
> "Jafer" <Jafer@.discussions.microsoft.com> wrote in message
> news:F408FF70-8F6A-47A5-88A4-9C001A3AE7C5@.microsoft.com...
>
>|||Jafer,
Now that I think about it. It does not make sense that this would work. You
will have to create the table manually or via a create statement.
my apologies,
Yosh
"Jafer" <Jafer@.discussions.microsoft.com> wrote in message
news:FE8CAA43-B589-4E73-8D15-68E6A69C5EBD@.microsoft.com...
> Yosh,
> SELECT * INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\test.mdb';'admin';'', Orders) FROM #temp_orders.
> gives, incorrect syntax error near the key word OPENROWSET. I gave the
> exact
> location of the MDB file along with the table name in the file.
> Any thoughts?
>
> "Yosh" wrote:
>|||Yosh,
FYI... In my previous try, I created the table you mentioned in the SQL in
Access file before running the query and tried to export the records from a
table having the same structure.
"Yosh" wrote:

> Jafer,
> Now that I think about it. It does not make sense that this would work. Yo
u
> will have to create the table manually or via a create statement.
> my apologies,
> Yosh
> "Jafer" <Jafer@.discussions.microsoft.com> wrote in message
> news:FE8CAA43-B589-4E73-8D15-68E6A69C5EBD@.microsoft.com...
>
>