Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

ODBC connection to Access dB

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 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