Solved

ODBC connection to Access dB

Posted on 2008-10-31
5
840 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now