| Author |
Topic |
|
David Anderson
Starting Member
8 Posts |
 Posted - 2008-11-26 : 18:01:52
|
| I have a need to export tables from a SQL Server database to a Microsoft Access Jet database (.MDB file) for use as a back end to an existing Access 2003 application. Is there any way to do this? My SQL Server database sits on a webserver - a shared SQL Server 2005 facility provided by my Windows hosting ISP. I only have SQL Server Express and SQL Server Management Studio Express on my own PC.David |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-11-26 : 18:27:12
|
Yes. You can use openrowset.
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
David Anderson
Starting Member
8 Posts |
Posted - 2008-11-26 : 19:12:33
|
| Hi Peso,I'm not exactly an expert on SQL Server. Can you provide a simple example of how and where you would use openrowset to export the total contents of a single table?David |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 19:42:54
|
| http://msdn.microsoft.com/en-us/library/ms190312.aspx |
|
|
|
David Anderson
Starting Member
8 Posts |
Posted - 2008-11-27 : 11:33:47
|
| Hi sodeep,Your quoted article is way over my head. For example, the Remarks section contains the following:"OPENROWSET can be used to access remote data from OLE DB data sources only when the DisallowAdhocAccess registry option is explicitly set to 0 for the specified provider, and the Ad Hoc Distributed Queries advanced configuration option is enabled. When these options are not set, the default behavior does not allow for ad hoc access.When accessing remote OLE DB data sources, the login identity of trusted connections is not automatically delegated from the server on which the client is connected to the server that is being queried. Authentication delegation must be configured. For more information, see Configuring Linked Servers for Delegation".I suspect the above means I need to ask my ISP some questions, though I'd be struggling to ask them in a coherent manner. Are you able to clarify this stuff for me? Is it likely to be a problem for my ISP to accept ad hoc queries on my portion of his SQL Server 2005 installation?It would greatly help me if someone could point me towards any simple code samples relevant to an ASP.NET developer (using VB.NET).David |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-27 : 11:49:59
|
| Sorry I mean Opendatasource:(without linked server problems)http://msdn.microsoft.com/en-us/library/ms179856.aspx |
|
|
|
David Anderson
Starting Member
8 Posts |
Posted - 2008-11-27 : 12:17:26
|
| I am beginning to think that I didn't explain myself very well in my OP. I have developed an ASP.NET website that uses a SQL Server database. I have no problem in accessing the database; my app has lots of interaction with the database, as you would expect.Are you suggesting that the only way for me to export all my SQL Server tables to an Access database is to write some additional code on my website that will read the contents of each table in turn and then rewrite this data into new Access tables? I guess I might be able to do that, assuming that ASP.NET offers me the tools to write to a Jet database (.mdb file). However, I was really hoping to find a utility program that would save me from having to write any code. |
|
|
|
David Anderson
Starting Member
8 Posts |
Posted - 2008-11-27 : 12:28:31
|
| Would all this be easier if I bought Visual Studio 2008 Professional Edition to replace VWD Express?Assuming that this package contain SQL Server Integration Services (SSIS) would that help with getting data out of my ISP's SQL Server 2005, or would I have to use the older DTS tools? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-27 : 12:48:59
|
| I am not sure SQL Express edition supports Export/Import wizard. You could have used that to transfer to Acess . |
|
|
|
David Anderson
Starting Member
8 Posts |
Posted - 2008-11-27 : 13:23:41
|
| Is that a yes to the two questions in my last post, then? In other words, does VS2008 Pro contain SSIS and does SSIS contain the wizard that can export to an Access Jet database (it's important that it can export to an MDB file rather than an MDF file)?SQL Server 2008 Express does not support SSIS or DTS according to this article:http://msdn.microsoft.com/en-us/library/bb500440.aspx |
|
|
|
ajit502
Starting Member
6 Posts |
Posted - 2008-12-03 : 06:15:50
|
| . |
|
|
|
|
Can I export tables from SQL Server to Access?