Solved

ODBC connection to Access dB

Posted on 2008-10-31
5
848 Views
Last Modified: 2013-11-28
I need to write reports for an Access dB.  I have no skills in Access reports, or at least they are very poor at this time.  What I am good at are t-sql queries.  All of my SQL dB reports are written in t-SQL then moved to VS 2005 Reporting services.

So what I did in reporting services is... created a Datasource to the Access database.  I have on my development box an ODBC System DSN which is what I used to configure the datasource in reporting services.  I wrote a simple query where I list some data from my access data.  When I deploy the report to my production server where everyone can run the report I get the error...

An error has occurred during report processing.
Cannot create a connection to data source 'AdTrack'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors .

I am thinking my production server needs an ODBC System DSN to the 'AdTrack' access database.  So I created it like I did on my development box.  It still doesn't work.

So, I want to come at it from a different angle.  I want my Server management studio to write direct t-sql queries to the access database.  I created a linked server.   I get this error when I run the query I wrote in my reporting services.....

OLE DB provider "MSDASQL" for linked server "ADTRACK" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

I am probably waaaayyyy over complicating things but I really want to write reports and deploy them through reporting services.

I hope I am just missing the basic configuring the ODBC but I don't know.  Please look at my files and code and tell me how to gain access to this dB and get some reports written.


Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "ADTRACK".


SELECT DISTINCT ADTRACK.[Ad Track].dbo.Account.[Account-Name]
, ADTRACK.[Ad Track].dbo.[Ad History].Date
, ADTRACK.[Ad Track].dbo.[Ad History].Price
, ADTRACK.[Ad Track].dbo.Brand.Brand
, ADTRACK.[Ad Track].dbo.Brand.Type
, ADTRACK.[Ad Track].dbo.Brand.Category
, ADTRACK.[Ad Track].dbo.[Size].[Size-ID]
, ADTRACK.[Ad Track].dbo.Format.[Format-Name]
, ADTRACK.[Ad Track].dbo.[Ad History].Date
, ADTRACK.[Ad Track].dbo.Brand.Distributor
, ADTRACK.[Ad Track].dbo.Account.Supervisor
FROM ADTRACK.[Ad Track].dbo.[Size] INNER JOIN 
	(ADTRACK.[Ad Track].dbo.Format INNER JOIN 
		(ADTRACK.[Ad Track].dbo.Brand INNER JOIN 
			(ADTRACK.[Ad Track].dbo.Account INNER JOIN ADTRACK.[Ad Track].dbo.[Ad History] ON ADTRACK.[Ad Track].dbo.Account.[Account Number] = ADTRACK.[Ad Track].dbo.[Ad History].[Account Number]) 
		ON ADTRACK.[Ad Track].dbo.Brand.[Brand ID] = ADTRACK.[Ad Track].dbo.[Ad History].[Brand ID]) 
	ON ADTRACK.[Ad Track].dbo.Format.[Format-Id] = ADTRACK.[Ad Track].dbo.[Ad History].[Format-Id]) 
ON ADTRACK.[Ad Track].dbo.[Size].[Size-ID] = ADTRACK.[Ad Track].dbo.[Ad History].[Size-ID]
WHERE (((ADTRACK.[Ad Track].dbo.[Ad History].Date)> '1/1/2008' And (ADTRACK.[Ad Track].dbo.[Ad History].Date)<'2/28/2008'))
ORDER BY ADTRACK.[Ad Track].dbo.Brand.Type DESC 
, ADTRACK.[Ad Track].dbo.Brand.Category
, ADTRACK.[Ad Track].dbo.Brand.Distributor
, ADTRACK.[Ad Track].dbo.Account.Supervisor

Open in new window

linked.bmp
ODBC.bmp
0
Comment
Question by:tjwent69
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 4

Expert Comment

by:joevi
ID: 22851232
In Linked Server Properties try changing the Data Source to the mdb path (L:\Ad Track.MDB)
0
 

Author Comment

by:tjwent69
ID: 22851283
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ADTRACK" returned message "'L:\Ad Track.MDB' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ADTRACK".

It saw the change.  :-)
0
 
LVL 4

Expert Comment

by:joevi
ID: 22851433
use quotes around the path ("L:\Ad Track.MDB"). Ideally a UNC to a shared folder.
0
 

Author Comment

by:tjwent69
ID: 22852468
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ADTRACK" returned message "Not a valid file name.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ADTRACK".

I added the screen shot of the path where the file is.
path.bmp
0
 

Accepted Solution

by:
tjwent69 earned 0 total points
ID: 23226139
The solution was to enter the datasource like \\Server-name\FolderName\Ad Track.mdb.  Once I did this I was then able to then query the access db in SMS.  However,  if I create the same linked server in more than one server, like my development and my production.  One will say that it is is use.  I give up.  I am going to migrate it to SQL everyday and keep reading and maybe one day I will find the answer.  Its probably somewhere in the initial install of SQL that will allow me to do what I want.
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question