PDA

View Full Version : sql updating



altrove
10-28-2006, 11:01 AM
:hi:
Excel 2002
I've some worksheets loading data from Access.
How can I update the source file when I move the mdb file
or I copy the xls file to another pc?
Help
Thanks

Jacob Hilderbrand
10-28-2006, 12:18 PM
How is the data being loaded? If you are using VBA, you can make the db path a variable and just change that, or you could even prompt the user for the path if it can't be found.

Moving the Excel file should not matter so long as you still have access to the database and it is at the same path (assuming we are talking about networked PCs).

altrove
10-28-2006, 12:41 PM
My code:
Sheets("sheet1").Select
Application.Goto Reference:="myQuery"
Selection.ClearContents
myPath = ActiveWorkbook.Path & "\myDatabase.mdb"
Selection.QueryTable.SourceDataFile = myPath
Selection.QueryTable.Refresh BackgroundQuery:=False


this code returne an error message :-(

Jacob Hilderbrand
10-28-2006, 02:50 PM
What is the error? And does the database exist at that location?

altrove
10-28-2006, 10:26 PM
I use MsgBox to get the new path:
MsgBox "The data source connection is: " & Selection.QueryTable.SourceDataFile
and it returns the new path.
But when I try to refersh the query in the next line:
Selection.QueryTable.Refresh
it returns the old path and in information warning (sorry for my translation from english):
(title) Access to ODBC driver not possible.

"OLD PATH" is not a valid path.
Be sure it is correct the name of the path (it reports the old one)
and you're connected to the server where the file is located.

XLGibbs
10-29-2006, 07:28 AM
Are the excel file and the access file in the same location?

Otherwise, using ActiveWorkbook.Path would not be the right way to specify the path.

It may be more practical to place/create the query table directly into the VB Code so you can have more control over the datasource. Obviously, if the Access file stayed in the same location, things would be much,much,much easier.

altrove
10-29-2006, 10:01 AM
Yes I have the mdb and the xls files in the same location.


It may be more practical to place/create the query table ...
I just need to update the path to let the querytable refesh.

XLGibbs
10-29-2006, 10:06 AM
If you go in to the SQL window of the MS Query used to populate the table, you will likely see that even if you change the path as indicated, it won't change in the actual stored Query from Access.... The SQL window would show a From C:\My Documents\.....\......etc followed by the tables Alias which appears everywhere else in the SQL. My guess, and experience, is that the SQL statement stored/saved with the query table does not actually change.


Which is why I mentioned that you may elect to build the SQL string in the Code rather than have a saved query.


You can essentially duplicate the effect of the stored query table right inside the VB code, including the connection string to the database which will make it more portable.