Change an ODBC connection on the fly?

MarkReo

New Member
Joined
Mar 24, 2002
Messages
1
Is there a way to force an existing Microsoft Query to point to a different relational database. In other words, in an environment with multiple AS/400's, is it possible to create a flexible ODBC connection that can download data based on user selection as to data origin.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
On 2002-03-25 08:25, MarkReo wrote:
Is there a way to force an existing Microsoft Query to point to a different relational database. In other words, in an environment with multiple AS/400's, is it possible to create a flexible ODBC connection that can download data based on user selection as to data origin.

I guess possible it is by input box or what ever to select source, but il be blund ODBC is flaky enough and not that good, upsetting she she will only cry ande go shopping for some cloths or a hair do!

I suggest having many codes say in userfor and select as needed each connection, should not be hard if you kopen3live via MS Quesry ODBC driver into excel can record and edit as nneds be.. i record to get ODBC connect lines perfect each time.

hope this hekp a little..

PS As400??? Good system, funny i wrote a debtors report download for a company in AS400 on ODBC, fine kit, but watch them copy and paste size boxes you draw... else bits go missing never got used to boxes to copy from higlight lines..
 
Upvote 0
I'm not familiar with ASA400 but here is a connection string I found in a help file. Maybe it will give you an idea of what you need to do?

Creating a DSN 'On the Fly'
So far, each of our connections has assumed that we have previously defined a Data Source Name (DSN). The DSN describes a data source, and the standard settings to be used when attaching to it. However, this can be limiting in some circumstances—especially when attaching to data sources that are dynamic in nature.

For example, let’s take a situation where the user connects to several servers, depending on the information they need. We could setup an ODBC Data Source Name definition for each server and selectively attach to it. A better way is to create the connection 'on the fly', without using a DSN:

ServerName = Request.QueryString("ServerName") 'from the submitted page
DBConn.Open "Driver={SQL Server};Server=" & ServerName & _
";UID=sa;PWD=;WSID=MAINFRAME;Language=us_english;Database=Forum;DSN=;"
...
In this example we create a definition on the fly, using the server name from a form submitted by the user to determine which SQL Server to attach to

Tom
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top