?
Solved

ODBC connection to Access dB

Posted on 2008-10-31
5
Medium Priority
?
856 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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 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