Solved

ODBC connection to Access dB

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Running Very Slowly on Windows 7 PC 27 61
SQL JOIN 6 39
subform does update on ms/access 2 16
sql calculate averages 18 32
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

746 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

12 Experts available now in Live!

Get 1:1 Help Now