Hi,
User working with Windows XP, has data in Excel. When she wants to refresh the data, which is linked to an external resource database in MS Access, she gets the error which is mentionned in the title. It seems that she has moved the database on another shared drive on the network (from I:\... to Y:\...) but I'm not able to change this link. Each time when I refresh and browse to the new drive letter and select the new location of the database, it returns with the same error message.
Excel Version is 2000.
Access Version is 97.
Can somebody advice?
Thx
Geert

hi frend

did u get the solution of your problem

i have the same problem as the data has been moved from one drive to the other drive
answers wil be appreciated ..thanks

If you using an ODBC driver source you need to re-configure the ODBC Data Source link. You can check this and configure it in using the "Data Sources (ODBC)" tool found in the Windows Control Panel Menu:

'Control Panel ->Administrative Tools->Data Sources (ODBC)'

ODBC Drivers can be set for the User Machine, System, or a File source. It can also be defined in a VB Script. You will have to find where the ODBC path is defined and correct the path there.

i appreciate ur quick reply

but my still struct with empty head
hhere is my code :


Public strSql As String
Public cConnection As ADODB.Connection

'Open ADO connection if not currently opened
Public Function fOpenDatabaseConnection()
Dim dbConnectionString As String
Dim filepath As String
On Error GoTo Err_fehler

filepath = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "\"))
If cConnection Is Nothing Then
Set cConnection = New ADODB.Connection
End If
If cConnection.State = 0 Then
'open connection
cConnection.CommandTimeout = 60
dbConnectionString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=False;DBQ=" & filepath & "Triple Check Macro_17OCT07.xls"
cConnection.Open dbConnectionString
End If

Exit_Err_fehler:

Exit Function

Err_fehler:
MsgBox Err.Description
Resume Exit_Err_fehler

End Function

'Returns recordset for sql string
Public Function fRetrieve(strSql As String) As ADODB.Recordset
Dim rst As New Recordset

On Error GoTo Err_fehler

fOpenDatabaseConnection
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient 'what does this do?
rst.Open strSql, cConnection, adOpenStatic, adLockBatchOptimistic

Exit_Err_fehler:
rst.ActiveConnection = Nothing
Set fRetrieve = rst
Set rst = Nothing
Exit Function

Err_fehler:
MsgBox Err.Description
Resume Exit_Err_fehler

End Function

'closes ADO connection
Public Function fCloseDatabaseConnection()
On Error GoTo Err_fehler

If Not cConnection.State = 0 Then
cConnection.Close
End If
Set cConnection = Nothing

Exit_Err_fehler:

Exit Function

Err_fehler:
MsgBox Err.Description
Resume Exit_Err_fehler
End Function

..............

i have checked the odbc connections in the administrative tools but found nothing there

i would appreciate if u can help me out
thanks

Sam, In your sample VB you are using code in an Excel File to access data from another XLS file. At least that is how the path is defined. Geert's original post stated a problem in accessing Excel from MS Access 97.

From the code you posted the path to the data file is being set on this line partly:

filepath = Left(ActiveWorkbook.FullName, InStrRev(ActiveWorkbook.FullName, "\"))

This is setting the path of the active file to identify the path of the sorce file. In otherwords you must (1) ensure both files are in the same directory. (2) be sure the active XLS file is a file in the same directory as your data file at the time the code is executed. Most likely you will be running the code in the active file.

You may want to look into doing this using ADO (ActiveX Data Objects) it gives you a lot more control and is faster than ODBC.

You need to add references to your project for "OLE Automation" and Office 11 Data Objects. You can use many standard ADO commands in Excel VB. http://www.w3schools.com/ado/ado_ref_command.asp

Yes ADO is better than ODBC