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

I get a run time error "subscript out of range"
do i need to change something in the macro you wrote. I can imagine that
some parts have to be changed to the real names, but which parts are that

"Joel" skrev:

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

I was returning the data (SQL and connection) to "Sheet2". Change Sheet2 to
any sheet in the workbook or add new worksheet.

"Ronald" wrote:

I get a run time error "subscript out of range"
do i need to change something in the macro you wrote. I can imagine that
some parts have to be changed to the real names, but which parts are that

"Joel" skrev:

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

i changed "sheet2" to an empty worksheet and i got:
ODBC;DSN=VismaBusiness;Description=Visma Business Installed SQL
Server.........
in my first row, but no further data. Still no possibility to edit the
query. It is the DSN that has to be changed to a new one.

"Joel" skrev:

I was returning the data (SQL and connection) to "Sheet2". Change Sheet2 to
any sheet in the workbook or add new worksheet.

"Ronald" wrote:

I get a run time error "subscript out of range"
do i need to change something in the macro you wrote. I can imagine that
some parts have to be changed to the real names, but which parts are that

"Joel" skrev:

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

The SQL statement is the important item. The SQL is the difficult item to
replicate. You can't edit the old connecttion, but you can create an new
connection and then copy the SQL from the old query.

I can create a macro the will make the new connection and copy the old SQL
to a new SQL or you can do it manually.

"Ronald" wrote:

i changed "sheet2" to an empty worksheet and i got:
ODBC;DSN=VismaBusiness;Description=Visma Business Installed SQL
Server.........
in my first row, but no further data. Still no possibility to edit the
query. It is the DSN that has to be changed to a new one.

"Joel" skrev:

I was returning the data (SQL and connection) to "Sheet2". Change Sheet2 to
any sheet in the workbook or add new worksheet.

"Ronald" wrote:

I get a run time error "subscript out of range"
do i need to change something in the macro you wrote. I can imagine that
some parts have to be changed to the real names, but which parts are that

"Joel" skrev:

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

If you want to, you may write a macro, otherwise please explain me what to
manually (if you already haven´t :-))

"Joel" skrev:

The SQL statement is the important item. The SQL is the difficult item to
replicate. You can't edit the old connecttion, but you can create an new
connection and then copy the SQL from the old query.

I can create a macro the will make the new connection and copy the old SQL
to a new SQL or you can do it manually.

"Ronald" wrote:

i changed "sheet2" to an empty worksheet and i got:
ODBC;DSN=VismaBusiness;Description=Visma Business Installed SQL
Server.........
in my first row, but no further data. Still no possibility to edit the
query. It is the DSN that has to be changed to a new one.

"Joel" skrev:

I was returning the data (SQL and connection) to "Sheet2". Change Sheet2 to
any sheet in the workbook or add new worksheet.

"Ronald" wrote:

I get a run time error "subscript out of range"
do i need to change something in the macro you wrote. I can imagine that
some parts have to be changed to the real names, but which parts are that

"Joel" skrev:

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

The simpliest method is to manually create a new query. Select the database
from new server and pick any table. Then press next until Finish appears.
then on finish menu select "View Data or edit query in Microsoft Query". On
the Query menu select View - SQL. Copy the SQL from the workbook "A2" cell
and paste into SQL. close Query tool and follow remaining menues.

--------------------------------------------------------------------------------------------
Method 2 would be to use the macro below. The SQL statement was very long
so I had to make it into 3 peices.

Here is the data taken from cell A2

SELECT `0215 & 0064 parts received`.ID, `0215 & 0064 parts
received`.Program, `0215 & 0064 parts received`.`Procurement Part Number`,
`0215 & 0064 parts received`.`Upscreen Part Number`, `0215 & 0064 parts
received`.`Date Code`, `0215 & 0064 parts received`.`Date Received`, `0215 &
0064 parts received`.`Lot Review Date`, `0215 & 0064 parts received`.`Drawing
Revision`

FROM `C:\TEMP\PMI Part Log`.`0215 & 0064 parts received` `0215 & 0064 parts
received`


I add double quotes arount the strings. Also any data posted at this site
add a return after 80 characters so you would have to remove these extra
returns. SQL 1 - 3 are one line each on my PC. the SQL above on my PC is
one line. the SQL has to be shorten because the server will not recognize
long lines. Also in the CommandText statement below I added commas between
the 3 SQL statements.






Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/1/2008 by jwarburg
'

Sql1 = "SELECT `0215 & 0064 parts received`.ID, `0215 & 0064 parts
received`.Program, `0215 & 0064 parts received`.`Procurement Part Number`,
`0215 & 0064 parts received`.`Upscreen Part Number`, `0215 & 0064 "
Sql2 = "parts received`.`Date Code`, `0215 & 0064 parts received`.`Date
Received`, `0215 & 0064 parts received`.`Lot Review Date`, `0215 & 0064 parts
received`.`Drawing Revision`" & Chr(13) & "" & Chr(10) & "FROM `C:\TEMP\PMI
Part Log`."
Sql3 = "`0215 & 0064 parts received` `0215 & 0064 parts received`"

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\PMI Part Log.mdb;" & _
"DefaultDir=C:\TEMP;DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), Array("=5;")), Destination:=Range("A1"))
.CommandText = Array(Sql1, Sql2, Sql3)

.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

"Ronald" wrote:

If you want to, you may write a macro, otherwise please explain me what to
manually (if you already haven´t :-))

"Joel" skrev:

The SQL statement is the important item. The SQL is the difficult item to
replicate. You can't edit the old connecttion, but you can create an new
connection and then copy the SQL from the old query.

I can create a macro the will make the new connection and copy the old SQL
to a new SQL or you can do it manually.

"Ronald" wrote:

i changed "sheet2" to an empty worksheet and i got:
ODBC;DSN=VismaBusiness;Description=Visma Business Installed SQL
Server.........
in my first row, but no further data. Still no possibility to edit the
query. It is the DSN that has to be changed to a new one.

"Joel" skrev:

I was returning the data (SQL and connection) to "Sheet2". Change Sheet2 to
any sheet in the workbook or add new worksheet.

"Ronald" wrote:

I get a run time error "subscript out of range"
do i need to change something in the macro you wrote. I can imagine that
some parts have to be changed to the real names, but which parts are that

"Joel" skrev:

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

Hi,

I did the manual option and it worked. You saved us a lot of time.

Thanks,

Ronald

"Joel" skrev:

The simpliest method is to manually create a new query. Select the database
from new server and pick any table. Then press next until Finish appears.
then on finish menu select "View Data or edit query in Microsoft Query". On
the Query menu select View - SQL. Copy the SQL from the workbook "A2" cell
and paste into SQL. close Query tool and follow remaining menues.

--------------------------------------------------------------------------------------------
Method 2 would be to use the macro below. The SQL statement was very long
so I had to make it into 3 peices.

Here is the data taken from cell A2

SELECT `0215 & 0064 parts received`.ID, `0215 & 0064 parts
received`.Program, `0215 & 0064 parts received`.`Procurement Part Number`,
`0215 & 0064 parts received`.`Upscreen Part Number`, `0215 & 0064 parts
received`.`Date Code`, `0215 & 0064 parts received`.`Date Received`, `0215 &
0064 parts received`.`Lot Review Date`, `0215 & 0064 parts received`.`Drawing
Revision`

FROM `C:\TEMP\PMI Part Log`.`0215 & 0064 parts received` `0215 & 0064 parts
received`


I add double quotes arount the strings. Also any data posted at this site
add a return after 80 characters so you would have to remove these extra
returns. SQL 1 - 3 are one line each on my PC. the SQL above on my PC is
one line. the SQL has to be shorten because the server will not recognize
long lines. Also in the CommandText statement below I added commas between
the 3 SQL statements.






Sub Macro1()
'
' Macro1 Macro
' Macro recorded 12/1/2008 by jwarburg
'

Sql1 = "SELECT `0215 & 0064 parts received`.ID, `0215 & 0064 parts
received`.Program, `0215 & 0064 parts received`.`Procurement Part Number`,
`0215 & 0064 parts received`.`Upscreen Part Number`, `0215 & 0064 "
Sql2 = "parts received`.`Date Code`, `0215 & 0064 parts received`.`Date
Received`, `0215 & 0064 parts received`.`Lot Review Date`, `0215 & 0064 parts
received`.`Drawing Revision`" & Chr(13) & "" & Chr(10) & "FROM `C:\TEMP\PMI
Part Log`."
Sql3 = "`0215 & 0064 parts received` `0215 & 0064 parts received`"

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\TEMP\PMI Part Log.mdb;" & _
"DefaultDir=C:\TEMP;DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout"), Array("=5;")), Destination:=Range("A1"))
.CommandText = Array(Sql1, Sql2, Sql3)

.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

"Ronald" wrote:

If you want to, you may write a macro, otherwise please explain me what to
manually (if you already haven´t :-))

"Joel" skrev:

The SQL statement is the important item. The SQL is the difficult item to
replicate. You can't edit the old connecttion, but you can create an new
connection and then copy the SQL from the old query.

I can create a macro the will make the new connection and copy the old SQL
to a new SQL or you can do it manually.

"Ronald" wrote:

i changed "sheet2" to an empty worksheet and i got:
ODBC;DSN=VismaBusiness;Description=Visma Business Installed SQL
Server.........
in my first row, but no further data. Still no possibility to edit the
query. It is the DSN that has to be changed to a new one.

"Joel" skrev:

I was returning the data (SQL and connection) to "Sheet2". Change Sheet2 to
any sheet in the workbook or add new worksheet.

"Ronald" wrote:

I get a run time error "subscript out of range"
do i need to change something in the macro you wrote. I can imagine that
some parts have to be changed to the real names, but which parts are that

"Joel" skrev:

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

Hi Joel,

what if a sheet has multiple SQL queries from different dbases. How should
we adjust your first macro to see all the info to create a new SQL as your
macro is based on one query per sheet, i assume (hope you understand what i
am asking)

Regards,

Ronald

Sub GetConnection()

RowCount = 1
For Each MyQuery In ActiveSheet.QueryTables
Sheets("Sheet2").Range("A" & RowCount) = MyQuery.Connection
Sheets("Sheet2").Range("A" & RowCount) = MyQuery.Sql
Next MyQuery
End Sub

"Ronald" wrote:

Hi Joel,

what if a sheet has multiple SQL queries from different dbases. How should
we adjust your first macro to see all the info to create a new SQL as your
macro is based on one query per sheet, i assume (hope you understand what i
am asking)

Regards,

Ronald