|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Export SQL Table to MS Access fileI 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 Show quote "Jafer" <Ja***@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 in
Access database? Show quote "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 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" <Ja***@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. > > > 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 Show quote "Jafer" <Ja***@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: > >> 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" <Ja***@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. >> >> >> 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? Show quote "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" <Ja***@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: > > > >> 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" <Ja***@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. > >> > >> > >> > > > 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 Show quote "Jafer" <Ja***@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: > >> 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" <Ja***@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: >> > >> >> 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" <Ja***@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. >> >> >> >> >> >> >> >> >> 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. Show quote "Yosh" wrote: > 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" <Ja***@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: > > > >> 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" <Ja***@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: > >> > > >> >> 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" <Ja***@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. > >> >> > >> >> > >> >> > >> > >> > >> > > > 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. Show quote "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 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" <Ja***@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 Show quote "Jafer" <Ja***@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.  
Other interesting topics
|
|||||||||||||||||||||||
