Hi,

For two weeks ago I have exported some data via Data, Import, New DBase.
This was retrieved from server X
Last week we changed host and therefore the Dbases are no longer on server X
but on server Y. When i try to update my data, it is not working ofcourse.
How can I change the source of the data so it is retrieving the data from
server Y, without creating a new Dabse request.

Thanks in advance

Ronald

I don't know if this is the best way, but it works. You can copy the SQL
statements from you old query to a new query.

1) get the Old SQL statements from worksheet menu. select worksheet where
query results are placed

a) Data - Import External Data - Edit Query
b) Press next until finish appears
c) select View Data or edit Query in Microsoft Query. Press finish
d) In Microsoft Query Menu select View - SQL
e) Copy SQL statements

2) Create new query selecting the database file on new server. Don't worry
about options because the SQL statements will change the request. Then
repeat 1) above and paste old SQL statements into New query.


"Ronald" wrote:

Hi,

For two weeks ago I have exported some data via Data, Import, New DBase.
This was retrieved from server X
Last week we changed host and therefore the Dbases are no longer on server X
but on server Y. When i try to update my data, it is not working ofcourse.
How can I change the source of the data so it is retrieving the data from
server Y, without creating a new Dabse request.

Thanks in advance

Ronald

Hi Joel,

it won't allow me to edit the query. I need to login on the old server which
is not operational any longer.

ronald

The macro below should be able to get the connection and sql data. Yo ucan
the create manuall a new connection and add the SQL. This could be automated
with a macro. Try this first.

Sub GetConnection()

Set MyQuery = ActiveSheet.QueryTables.Item(1)
Sheets("Sheet2").Range("A1") = MyQuery.Connection
Sheets("Sheet2").Range("A2") = MyQuery.Sql
End Sub

"Ronald" wrote:

Hi Joel,

it won't allow me to edit the query. I need to login on the old server which
is not operational any longer.

ronald

I am not a star with macros. if you have the time for it, can you guide me
step-by-step? it should be on the level : click on ..., write this...

Ronald

Go to worksheet where query is located. Then press Alt-F11. Copy and Paste
code into VBA sheet. Click with mouse anyway in the pasted code and press F5.

"Ronald" wrote:

I am not a star with macros. if you have the time for it, can you guide me
step-by-step? it should be on the level : click on ..., write this...

Ronald