Report Model from MS Access Database

Question

Post reply

Report Model from MS Access Database

brunm

SSCommitted

Points: 1792
More actions
January 13, 2010 at 4:47 am

#208947

Hi,
Is it possible to create (and deploy) a Report Model based on an Microsoft Access Database
i have managed to create my DSV AND SMDL files but when i go to deploy the Model i am getting the error
Error1Exception of type 'System.NotSupportedException' was thrown
Does anyone know if this is possible and if it is not possible if you could point the way to the Microsoft Article which states this as i cant find anything which says this can't be done
Your time and effort greatly appreciated
Brunzire

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

brunm SSCommitted Points: 1792 More actions · Answer 1

I found this article from Microsoft

http://msdn.microsoft.com/en-us/library/ms159219.aspx

which suggests that this is not possible.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Don't always believe Microsoft :-).

Try this and I'm pretty sure it'll work :

Create a linked server from SQL Server to the Access DB.

Then build a SP for the source of the data for the report(s).

Call the SP to display the data.

Assuming the SP returns what you need an nobody deletes the Access DB it should work.

Of course you can always cut the middle man and import all the required tables from access to SQL but that's not always possible to do a full or partial rewrite of the DBs.

HTH.

brunm SSCommitted Points: 1792 More actions · Answer 3

Thanks for the repsonse.

The Access database is password protected and i can't seem to create a linked Server for it

(i can create a linked server no problems for an access db that is not password protected).

The database is not mine to change/import to SQL Server.

i will probably Data Warehouse it as i see no easy solution.

The annonying thing is that the Report Model Project Template allowed me to basically do everything bar Deploying the Model.

it is a tiny Database and i thought i could save some time by Modelling it directly (1/2 Days Work) as oppossed to Data Warehousing it (2 Days Work)

Only postive is it give me some ammunication to fire at our Technical Architect as i double checked with him (he told me he had done it before) before i commenced Development

Thanks agains for your Response