MS Query Data Source Change

I have a series of saved queries that I use on an ODBC data source.  I would 
like to put them on other computers that may not have the same data sources 
but have different ones (tables are the same in each data source).  Right now 
I am editing the query in Notepad to change the data source and path to the 
correct one on the other computers but am wondering why it is not evident how 
to change this in the query itself.  Can it be done?  How?

TIA
0
rasinc (5)
7/26/2005 12:27:03 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
541 Views

Similar Articles

[PageSpeed] 1

I had a similiar problem which I solved with a form/macro. (I don't think you 
can point to a different source other than manually changing the code.)

1. Construct a from with 4 field: Old and new connection strings, old and 
new query.

2. When activating the form, populate the "old" fields with the data like 
this: (assuming you have selected the query before you activate the form)

OldConnection = Selection.QueryTable.Connection
OldQuery = Selection.QueryTable.CommandText

(If it is a Pivot Table, use this:
OldConnection = Selection.PivotTable.PivotCache.Connection
OldQuery = Selection.PivotTable.PivotCache.CommandText)

3. Write some code to populate the new files. For example:

NewConnection = Replace(OldConnection, "OldODBC", "NewODBC")

4. Add a button that approves of changing the connection and query strings:

Selection.QueryTable.Connection = NewConnection
Selection.QueryTable.CommandText = NewQuery

(If Pivot Table:
Selection.PivotTable.PivotCache.Connection = NewConnection
Selection.PivotTable.PivotCache.CommandText = NewQuery)

5. To make the change, select the query/pivot, activate the form, check that 
the new connection/query strings look allright and then click OK/Approve.

I don't know if this helps, but for me this saved hours of changing query 
strings.

/Marcus


"rasinc" wrote:

> I have a series of saved queries that I use on an ODBC data source.  I would 
> like to put them on other computers that may not have the same data sources 
> but have different ones (tables are the same in each data source).  Right now 
> I am editing the query in Notepad to change the data source and path to the 
> correct one on the other computers but am wondering why it is not evident how 
> to change this in the query itself.  Can it be done?  How?
> 
> TIA
0
7/26/2005 5:59:03 AM
Thanks.  I'll try your code when I get a chance.

"Marcus Langell" wrote:

> I had a similiar problem which I solved with a form/macro. (I don't think you 
> can point to a different source other than manually changing the code.)
> 
> 1. Construct a from with 4 field: Old and new connection strings, old and 
> new query.
> 
> 2. When activating the form, populate the "old" fields with the data like 
> this: (assuming you have selected the query before you activate the form)
> 
> OldConnection = Selection.QueryTable.Connection
> OldQuery = Selection.QueryTable.CommandText
> 
> (If it is a Pivot Table, use this:
> OldConnection = Selection.PivotTable.PivotCache.Connection
> OldQuery = Selection.PivotTable.PivotCache.CommandText)
> 
> 3. Write some code to populate the new files. For example:
> 
> NewConnection = Replace(OldConnection, "OldODBC", "NewODBC")
> 
> 4. Add a button that approves of changing the connection and query strings:
> 
> Selection.QueryTable.Connection = NewConnection
> Selection.QueryTable.CommandText = NewQuery
> 
> (If Pivot Table:
> Selection.PivotTable.PivotCache.Connection = NewConnection
> Selection.PivotTable.PivotCache.CommandText = NewQuery)
> 
> 5. To make the change, select the query/pivot, activate the form, check that 
> the new connection/query strings look allright and then click OK/Approve.
> 
> I don't know if this helps, but for me this saved hours of changing query 
> strings.
> 
> /Marcus
> 
> 
> "rasinc" wrote:
> 
> > I have a series of saved queries that I use on an ODBC data source.  I would 
> > like to put them on other computers that may not have the same data sources 
> > but have different ones (tables are the same in each data source).  Right now 
> > I am editing the query in Notepad to change the data source and path to the 
> > correct one on the other computers but am wondering why it is not evident how 
> > to change this in the query itself.  Can it be done?  How?
> > 
> > TIA
0
rasinc (5)
7/27/2005 6:09:02 PM
Reply:

Similar Artilces:

MS Outlook 2007
I am running MS Outlook 2007 on a Vista home lap top. When trying to delete files (or folders) in the Deleted Items folder, I get a message that "the Folder is full" and the items remain. I "right click" on Deleted Items, select "Empty 'Deleted Items' Folder", and I get the usueal warning msg that states "Are you sure you want to permanently delete all the items and subfolders from the 'Deleted Items' folder?" I click on "Yes". I then get a second warning msg that says "The folder is full." The ite...

Query SQL Syntax
I have 2 queries that I am building another query off of. I have on query with the name of project contacts in it. I have another query with all the associated contact info in it. I made a cutom colum in the query and used this syntax. DPM Phone:(Select [Business Phone] FROM [Contacts Extended] Where [Contacts Extended]![Contact Name]=[Target_Stores_MRRS_Extended]![DPM]) This is not working. Can anyone help or point me to a good resource for how to use the syntax correctly. Thanks, On Thu, 27 May 2010 16:15:01 -0700, Rob Hamlin <RobHamlin@discussions.microsoft.com>...

Stopping duplication within Data validation
I hope someone can help me with these problems! I am using Data Validation for selected vehicles on runs, but I need to stop people assigning a duplicate registration for seperate runs. The list for the data validation is in cells D91 to D120 and the entries are made in cells H3 to H47 I have tried the following: (Cell H2) =ISERROR(MATCH(H2,H3:H47,0)) (Cells H3 to H47) =ISERROR(MATCH(H3,INDIRECT("$D$91:$D$120"ROW()-1),0)) But validation throws a wobbly and doesn't like the entry (which is exactly as I copied from help!) Where am I going wrong?? ALSO - ...

Totals in the data table but not in the graph / chart
I am graphing data that consists of 4 parts and over 5 years. I'm using a stacked bar or an area chart and I do not want the totals to be charted. I do however, want the totals (both by the year and the 4 parts) or across the bottom and to the far right of the data table. I've chosen to have the data table under the graph. I can easily put the totals into excel but then it graphs the totals (which doubles everything). If I do not include the total when I choose the graph the graph simply leaves them out with no space to add them in... JHB Graph the total, hide t...

Use of MS Dynamics CRM in Utility Billing
Hello Friendz, I was wondering if we can easily use Microsoft Dynamics CRM 3.0 for 'Utility Billing System and Print Presentment'. This also involves use of handheld devices for reading meters and bill presentment. In case you know about a better solution for this requirement, please share that with me. -- Regards, Dave Banerjee Cell: 0060 17331 4676 | email: IfThenElseEndIf@msn.com | IfThenElseEndIf@GMail.com That depends on what you mean by "easily" ;-) CRM out of the box will provide a great, flexible platform to quickly develop a robust business solution. Fro...

MS Office 97 to MS Office 2007
I have been running Office 97 pro on Windows XP because I do not want to loose the shortcut bar that came with 97. Thing is that I have been thinking about installing the 2007 version of Office that I have had but never installed yet for above reasons. Question is can I install the 2007 version over the 97 version and have it update the programs but keep the shortcut bar?? I know there are probably other shortcut bars or whatever out there but as I asked I would rather keep the 97 one if possible. TIA Microsoft has several Knowledge Base articles on running multiple...

How to change newsletter layout
How can I change the layout of newsletters: add images, colors.... in the mail sent Thanks ...

Variable Allocations and Change in Fiscal Year
Hi, We have a situation where we changed the fiscal year and now some of our variable allocations that are based on YTD are not working. What do we need to do? Thanks, More specifics please. What do you mean they're 'not working'? Frank Hamelly MCP-GP, MCT, MVP East Coast Dynamics www.eastcoast-dynamics.com ...

Automatic selecting and sharing of data accross worksheets
Imagine this is Sheet 1 NAME EYES JOB TRAN. EYES ---------------------------------------------- John blue doctor car blond Sarah green nurse bike brown Dave brown doctor walk grey Mike blue nurse car blonde I would like Excel to automatically select all rows/info of doctors and copy it to Sheet 2. So sheet 2 would be: NAME EYES JOB TRAN. EYES ---------------------------------------------- John blue doctor car blond Dave brown doctor walk grey P...

Difference between MS EPM and MS Projects
What is the difference between MS EPM and MS projects Is it just project server and sharepoint added to MS projects together called MS EPM? Yes. EPM is Enterprise Project Management. Microsoft's solution is Project Professional plus Project Server which in turn uses SharePoint technology. -- Rod Gill Microsoft MVP for Project - http://www.project-systems.co.nz Author of the only book on Project VBA, see: http://www.projectvbabook.com "Project Mgt Noob" <Project Mgt Noob @discussions.microsoft.com> wrote in message news:A565E3DF-A7FC-4D2A-B2F1...

MS Exange connection
It deals with Outlook 2003, running under Win XP home: A friend has two ISPs, one with standard POP and SMTP servers, no problem there. The second is a remote MS Exchange server. He connects to both via Internet using ADSL connection. Question: how to configure the Exchange server connection such that it remembers his ID and PW? I could not find anything under just about any thinkable tab in Outlook, email accounts, etc. Any help will be greatly appreciated. -- JanAdam ...

how to join dynamic SQL results with another query
Hi Everybody, I'm using dynamic SQL to execute a pivot query for a sales report since the column names will change by month. The very last statement is "EXECUTE sp_executesql @query", where @query is my pivot query string. How do I join these results with another query so as to get one resulting dataset? For example, here's my "other query" results: SELECT 1 AS [CustId], 'ABC Corp.' AS [CustName] UNION ALL SELECT 2 AS [CustId], 'Microsoft' And here's my pivot query results: SELECT 1 AS [CustNum], 35.99 AS [Jan_Sales], 104.5...

Display query result in a form
I have form where 2 of the fields are "Name" and "Date of transaction". Every time I enter a 'Name' in a new record, I want a pop up, or 3rd field, to show the last 'Date of transaction' for that 'Name'. Thanks ...

Problem- add-in Access Outlook Add-in for Data Collection & Publis
I have a problem with Outlook 2007 and the add-in Access Outlook Add-in for Data Collection and Publishing. This add-in worked when I first installed Outlook 2007 when installing Office 2007 Enterprise. The add-in created a sub-folder in my Inbox named Data Collection Replies and worked well until about 6 weeks ago. Now I can’t get the add-in to work at all even though it appears in the list of COM.adds in Outlook 2007. More perplexing is the error message I now receive EVERY time I click on any email message to read it. The message is titled ‘Custom UI Runtime Error in...

Export Excel data to Lotus Notes 6.5 Address Book
anyone know how to export excel data into Lotus Notes 6.5 address book? I've tried importing from notes... but am not having much luck. thanks, LF ...

QUICKSELL Blank or Empty Fields/data
How should I treat an empty excel field on import with Quicksell? Example "Notes" <I don’t' have any data for this field>. Do I leave the field blank, or place a comma in the field, or put some text, or delete the column and don't include the field in the import? How should I treat "Last Sold" field if I do not have a history? Similar to above do I leave the field blank, or place a comma in the field, or put some text, or delete the column and don't include the field in the import? When I have a item unlike others with data what should I do if I ...

Icon change
I'm making a small Excel project. Is there a way to change the Excel icon on my project so the shortcut icon will be a little more personalized? Thanks Peter, Right click icon > Properties > Shortcut > Change icon would be a start Rob "Peter" <grouchyfd@bellsouth.net> wrote in message news:052101c3d729$314e4570$a301280a@phx.gbl... > I'm making a small Excel project. Is there a way to > change the Excel icon on my project so the shortcut icon > will be a little more personalized? > > Thanks You posted to more than one newsgroup. I think...

How do I do word count in MS Publisher?
Mike, Publisher doesn't have a word count; the work-around wound be manually count the words or you could try copy the text and paste into Word. -- Don - Publisher 2000� Vancouver, USA "Mike Finn" <Mike Finn@discussions.microsoft.com> wrote in message news:9EBF3083-D7EC-46E9-ADA2-E4616499399E@microsoft.com... > Why not just do a "Edit Story in Microsoft Word" instead of copy and paste? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Don Schmidt" <Don Engineer@PNB.Retired_...

Delete Query 03-27-10
Please Help - I have searched the posts and have found some ideas, but nothing seems to work. Here is the sql statement Error 3086 resolutions do not apply as far as i can determine DELETE DISTINCTROW DistributedAnalyst.*, DistributedByMaterial.Material FROM DistributedAnalyst LEFT JOIN DistributedByMaterial ON DistributedAnalyst.Material = DistributedByMaterial.Material WHERE (((DistributedByMaterial.Material) Is Null)); Thanks in advance On Sat, 27 Mar 2010 15:55:01 -0700, dmoney <dmoney@discussions.microsoft.com> wrote: >Please Help - I have searched the posts...

Data Validation List Names
I have Excel 2007 and am attempting to use the data validation tool. I have created several lists in a separate worksheet and named them (In the name box). My drop down and options have shown correctly. However, I now need to make a few changes to the lists. I added a few extra cells with data to one of the lists and would like these options included in the drop down field on the main spreadsheet. Can someone tell me how to re-name my 'source' (list or range of cells) so that the added cells are now being referenced as well and all options show in the drop down? ...

SBS 2003 and Outlook 2003 requesting data, freeze
I have two separate clients that are running the new SBS 2003 with XP workstations running Outlook 2003. Both randomly receive the messages: Outlook is requesting data from the server Outlook is trying to retrieve data from the Microsoft Exchange Server Client 1 Running 2 servers that each has a Dual GHz Xeon w/hyperthreading on with 4 gigs of ram. One server is running the SBS 2k3 and the other server is running a Win 2k3 as a terminal server. They are connected on a 1Gig backplane. 10 users total. Client 2 Running 2 servers. One server has a 2.GHz P4 w/hyperthreading on running SBS ...

MS Money 2005 Basics
I am new to MS Money after many years with Quicken. Money help offline and online, seems woefully short of detailed explanations o user scenarios. . Searching help for "Epay" yields nothing. Ca someone either direct me to a better help area or answer this In Bills what is the difference between Epay, APay, and Direct debit I see Apay is how i use the bill pay service of my bank. Is direc debit just my manual entry? What then is Epay? What might "Other payment type then be used for? Thanks The difference between them is this Epay is for electronic bill pay. You must have Bi...

MS CRM Certification question
Hi all, I have a question regarding the CRM certification? What, if any, certifications are there available with MS CRM? Is there anything like CRM Professional? Have I earned any certification if I passed the Applications Professional exam? T.I.A -- Kuba Skalbania [MCAD] ...

Sent items downloading problem in MS office outlook 2007
Hi all, In my client pc's i have configured the Microsoft office outlook 2007 after the configuration inbox mails are visible in inbox but sent items and other folders are not getting downloaded, my mail server is squirrel mail ,so please let me know the reason . POP3 or IMAP? "xpmate" <sreekanth.chn@gmail.com> wrote in message news:41cb29fe-c615-44b3-aa86-ea3eb607e2a5@g22g2000prf.googlegroups.com... > Hi all, > > In my client pc's i have configured the Microsoft office outlook > 2007 after the configuration inbox mails are visible in in...

MS Access
Why doesn't MS support MS Access for Macintosh? Since I work in an educational environment that's mostly PC with MS Office begin the main suite, it would only be logical that since the PC version of MS Office contains MS Access for database entry, the Mac version of MS Office would also have MS Access. In article <BE09D66D.5FB8%redclawx@hotmail.com>, Red Claw <redclawx@hotmail.com> wrote: > Why doesn't MS support MS Access for Macintosh? Since I work in an > educational environment that's mostly PC with MS Office begin the main > suite, it would only ...