Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    1

    Unanswered: ODBC Microsoft Access Driver Login Failed

    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

  2. #2
    Join Date
    Nov 2007
    Posts
    93

    same connection problenm

    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

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    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.
    ~

    Bill

  4. #4
    Join Date
    Nov 2007
    Posts
    93

    ODbc driver access login failed

    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

  5. #5
    Join Date
    Feb 2004
    Posts
    533
    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
    ~

    Bill

  6. #6
    Join Date
    Nov 2007
    Posts
    20

    ADO vs ODBC

    Yes ADO is better than ODBC
    Peak Consulting - Business Intelligence Experts

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •