Expert Advice - SQL server to Access Dump

[Domski74]

Hi everyone,

I'm no expert but a colleague of mine has stated that it's much harder to dump data from an SQL server into Access than to another SQL server, he's prone to trying to baffle people with technobabble so I just wanted to elicit some expert advice as to whether this is true?

His exact words were:

"The data is held in a SQL server which is dumped hourly. The dump file could be restored into another SQL server for reporting. We can create views to expose the data model.

Moving the data to Access would be a more complex development task which I wouldn’t recommend."

Is this correct?

Many thanks

 

[CameronM]

Data from SQL server can be retreived via ODBC directly into Access, if this suits your requirements. Then you can create reports to your hearts content with the bonus of have 'live' data, not even one hour old.

I routinely link tables from SQL Server so that I can utilise the great reporting tools in Access. Unless your collegue does not even have the ability to create an ODBC data source (which is really easy), I think he is using the old "Acess is evil because I don't know how to use it" excuse.

 

[CitizenKane]

you could also create a DTS package on the SQL server which would dump all the data you need into access very easily, all you need to do is specify one or 2 parameters and 'Hey Presto' your data is in access. the other option would be to right click on the database and just select export. This is the solution if you do not need to have live data but if you do want live data then I would recommend using an ODBC connector as the last post stated

Hope this helps

 

[MSAccessRookie]

Hi everyone,

I'm no expert but a colleague of mine has stated that it's much harder to dump data from an SQL server into Access than to another SQL server, he's prone to trying to baffle people with technobabble so I just wanted to elicit some expert advice as to whether this is true?

His exact words were:

"The data is held in a SQL server which is dumped hourly. The dump file could be restored into another SQL server for reporting. We can create views to expose the data model.

Moving the data to Access would be a more complex development task which I wouldn’t recommend."

Is this correct?

Many thanks

I think someone is trying to Mark his territory and protect his job.

Of course the data can be moved, and of course it is not much more difficult no matter which solution you choose. The biggest difference will be the speed of update. Since SQL Server / SQL Server Updates require no ODBC Links, or Transformation of Data Types, it is easy to see how they could be faster than SQL Server / MS Access Updates. As CameronM pointed out, "you can create reports to your hearts content with the bonus of have 'live' data, not even one hour old"

 

[gemma-the-husky]

mind you if you try to copy the data by copying the table, often you just end up copying the link - but if you do a maketable query you will collect all the sql data into your access dbs