excel - acess - cannot redirect to different data location

[inncoggnitto]

i have excel spreadsheets pulling data from an access db. i need to move the db to another server. i have tried everything to get the queries (about 40 of them) redirected to the new server w/o luck.

i have deleted the system odbc driver and the queries will still pull the data. it seems that the path to the data on the network gets incorporated into the queries since even w/o the odbc driver configed they will work, but if i disconnect the network drive they don't.

i have also reconfiged the driver to point to the new server and it still pulls from the old location. i have moved the spreadsheets to the new server with the odbc driver on the server pointing to the new location and no luck.

i cannot find anywhere to edit the query directly to redirect it to the new data location.

any suggestions would be appreciated.

 

[KenWright]

Not really touched ODBC but Googled this which seemed to resonate with what you are trying to do - any help?

For your info, here is the code I used to change the 
source of the mdb: 

Public Sub ResetPivotTableConnection() 
  Dim pc As PivotCache 
  Dim pt As PivotTable 
  Dim str As String 
  Set pt = Sheet2.PivotTables("PivotTable­2") 
  Set pc = ThisWorkbook.PivotCaches(pt.Ca­cheIndex) 
  'Debug.Print pc.Connection 
  str = "ODBC;DSN=MS Access Database;" 
  str = str 
& "ODBC;DBQ=c:\....Summ_Data.mdb­;DefaultDir=c:\....;Driver= 
{Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS 
Access;MaxBufferSize=2048;MaxS­canRows=8;PageTimeout=5;SafeT 
ransactions=0;Threads=3;UID=ad­min;UserCommitSync=Yes;" 
  pc.Connection = str 
End Sub

Regards
Ken............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[inncoggnitto]

it might if i was doing a vb app but i am just using the query editor built in to excel. i know in crystal reports there is a way to redirect the db source.

it seems like the path is being stored in the queries. i have even tried deleting and recreating the odbc connection and then making a new query to test. the new query will point to the new data location but the old queries will still point to the old data even though the odbc config does not point there. if i disconnect the network drive to the old data the queries break, but if i reconnect the drive ta da, they work.

 

[klmorgan]

I had some probs a year or so back and found that the info was in .DQY files.

The following was sent to me from XLBO at the time:

On XP, they are under Control Panel>Administrative Tools

The DQY file usually holds connection info. If you right click on the .DQY file, you should get an option to edit in notepad -mine looks like this:

XLODBC
1
DSN=KnowledgeQuery;Description=Punch Pub KMS Database;UID=barracg;APP=Microsoft® Query;WSID=FRA64544;DATABASE=KnowledgeQuery;LANGUAGE=British;Network=DBMSSOCN;Trusted_Connection=Yes
SELECT DISTINCT dbo.Site.Prop_Ref_No, blah blah blah

The bits in bold are important as they hold the userID info and the trusted connection means that it doesn't prompt for a password (I think)

I have been retiered for a year now so I'm rusty, hope it helps!

Regards

Keith