Report Builder 3 connection to an Access 2016 Database

[Deb_MI]

I feel like this should be so simple but I'm struggling. I want to use data in an Access 2016 Database to create reports on Report Builder. When I'm not connected to the report server the connection string works and I get data. When I connect it to the report server I get an error: "Unable to connect to the data source. Please verify the connection properties and credentials". When I click "details" I get either an architecture error (which I believe indicates a 32bit to 64bit error) or "Data source name not found and no default driver specified". Our Access is 32 bit and the ODBC Driver I'm using is 32 bit. Again, it works when Report Builder is not connected to the report server but errors when connected to the report server. I've selected that I'm using an ODBC connection type. I think the error is in the connection string but I can't find any help on what a valid connection string would be. I don't know where to look/change if the problem is actually that something is 64 bit. It seems like it should be so simple for a Microsoft Report Builder on a Microsoft Server to pull data from Microsoft Access. Anyone have any ideas?


Thanks!

 

[kray4660]

I hate to ask the stupid question. Have you created a 64-bit ODBC (using the same name as the 32-bit ODBC) to see if that fixes the issue?

 

[Deb_MI]

Thank you for responding. No stupid questions, trust me. Yes, I created a 64-bit ODBC. When I went to installed AccessDatabaseEngine_X64 it complained about the 32Bit Office installed. I uninstalled Office, installed the driver, then reinstalled Office 32Bit. Same architecture error. I then tried to install the 32Bit version of AccessDatabaseEngine but it complained that the 64Bit was already loaded. I uninstalled the 64bit and reinstalled the 32 bit. Before I answered this question I decided to google again and found an entry on how to have both loaded (using /passive in the cmd line) but it didn't work - still got the message that the 32Bit version of Office was loaded so it wouldn't install the 64bit AccessDatabaseEngine. The info I googled wasn't for Office 2016 so not sure if there is now another way.

 

[Deb_MI]

For instance, when I have a Data Source simply set up as Connection Type = ODBC and the Connection String is DSN=QueryDB. This set up will run when it is not on the Report Server. When I connect to the Report Server the error is:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

So I've tried using a connection string beyond simply "DSN=QueryDB". On the Microsoft Page where you can download AccessDatabaseEngine_x64 it has: Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file. I've tried that and got the same error message.

 

[dhookom]

I would try some connection strings from this web page.

Is the Access file on the Report Server?

What path are you using in your connection string?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Deb_MI]

I've tried to get to

http://www.connectionstrings.com

before (I've seen it referenced) but the page won't load ("Problem Loading Page" in FireFox and "Hmm...can't reach this page" in Edge).

The Access File is not on the Report Server. I'll make a copy of the database and put it there to test. Today wasn't a good day for that but hopefully tomorrow.

As to the path, I've tried with a mapped drive ("Q:\QueryDB") and the UNC path (\\<ServerName>\<FolderName>\QueryDB).

I like the idea of moving the database. I'll give it a try but if it works, that opens up other problems since the database is being accessed right now by a number of Crystal Reports. I'd love to just have everything in SQL but we have a slightly antiquated data source that won't be moving there anytime soon.

Thank you for your ideas.

 

[dhookom]

You might also attempt to create a linked server from SQL to the Access database and then use it for reporting.

The connection strings site works with my Edge and all other browsers including Chrome, IE, and FireFox.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[kray4660]

What I was suggesting to have a 32-bit and 64-bit ODBC of the same name. We have to this for certain SAP products. Thought it might be a similar issue.

 

[Deb_MI]

So I tried to connect to

http://www.connectionstrings.com

from my phone after turning off WiFi and it connected. Turns out their website is hosted on a server in the Netherlands and our fire guard was blocking it but not telling me that. I haven't had a chance to dig in yet but I will. I also like the idea of a linked server. I tried doing that to link to the antiquated database but didn't think to try it with the access database.

Kray4660, I don't know how to get both a 32-bit and 64-bit ODBC DSN set up at the same time. I don't know how to load the driver (AccessDatabaseEngine) in both the 32-bit and 64-bit versions at the same time. It makes me uninstall the other. I saw this suggestion online somewhere and wasn't able to come up with a solution.

Thank you everyone for your thoughts.

 

[kray4660]

Well I am working with Windows 7 (64-bit) and there is a 32-ODBC application and a 64-bit ODBC application (can't have them open at the same time). The 32-bi ODBC is at c:\Windows\SysWoW64. It is called odbcad32.exe. The 64-bit ODBC is in c:\Windows\System32 and it call odbcad32.exe. For our SAP products we run each ODBC executable and set up the ODBC connection exactly the same in both places.

Unfortunately I do not how to set both on other OS's except on Windows Server 2012.