Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC problem (Excel/Access 2003)

    Hi everybody:

    I'm writing an Access database application with an Excel workbook user interface (not my choice). Each row in the main range of the workbook represents an investment that will be saved to the tables. The users want to be able to add new investments, so I want my app to hit the database table to get the next Investment ID number to insert into a newly added row within that range. The rest of the time, I want the users' workbooks to be disconnected from the database.

    I can trap their adding a row in Excel and get the row insert location. I have used the wizard to create a datatable using an ODBC Jet connection and SQL query that gets the next InvID from my Access table. Fine. Except that this datatable and ODBC connection seem to stay open whenever the Excel workbook is open. That locks up my Access back-end file! The only way I have been able to re-open the Access back-end tables was to delete the Excel datatable or close the workbook. So, I need to understand how to create a connection that will be available only when necessary and stay closed the rest of the time, while users keep working in Excel.

    Is there a better way than ODBC? Or, is there VBA code I should use to close the ODBC connection?

    Please advise.

    Thank you, in advance, for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: ODBC problem (Excel/Access 2003)

    The new database query wizard creates a connection that remains open as long as the workbook is open.
    Use DAO or ADO instead. For example, you can open the database in DAO, open a recordset, copy its records into the worksheet using the CopyFromRecordset method of a range, then close the recordset and close the database.
    To append a record, open the database, open a recordset, add a record using the AddNew and Update methods of the recordset, then close the recordset and close the database.

    Keep in mind that opening and closing the database uses lots of resources, so if users create several new rows within a short time, performance will suffer.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC problem (Excel/Access 2003)

    Thanks, Hans.

    I do not anticipate that this functionality will be needed very often. It will be needed whenever a new investment is funded, which takes time. Each user might need it once or twice a month. But I cannot control it from the database using DAO, since the users will be inserting the new investment information on their spreadsheets, which they use to calculate returns. (In the best of all possible worlds, I would build my database application and provide users with a form they would use to update data, but this is not going to be possible.)

    I need a way for the Excel front-end to hit the database tables to retrieve the next InvID number from the database query and then insert it into a certain column in the inserted row. Can you point me to Excel VBA code that would do that? Then, when I update the db tables from the spreadsheets, I can capture the new ID number and insert the record into the related tables.

    Thank you for your help.

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 85 Times in 81 Posts

    Re: ODBC problem (Excel/Access 2003)

    Here's a sample using ADO (you need to set a reference in your workbook to the Microsoft ActiveX Data Objects 2.x Library):
    <pre>Sub GetNextIDFromAccess()
    ' Sample demonstrating how to return an ID from an Access db
    Dim cn As ADODB.Connection, strQUery As String, rst As ADODB.Recordset
    Dim strPathToDB As String, strFormula As String, I As Long
    Dim lngNextNum As Long

    strPathToDB = "Cathdatabase.mdb"

    Set cn = New ADODB.Connection
    With cn
    .ConnectionTimeout = 500
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strPathToDB & ";"
    .Open
    .CommandTimeout = 500
    End With

    strQUery = "SELECT Max([Case Number]) FROM ACQ"

    Set rst = New ADODB.Recordset
    With rst
    .Open strQUery, cn, adOpenForwardOnly, adLockPessimistic, adCmdText
    If Not .EOF Then lngNextNum = rst(0) + 1
    .Close
    End With

    Debug.Print lngNextNum
    Set rst = Nothing
    cn.Close
    Set cn = Nothing
    End Sub
    </pre>


    HTH
    Regards,
    Rory
    Microsoft MVP - Excel.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC problem (Excel/Access 2003)

    Cool! You're the best.

    Thanks!

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC problem (Excel/Access 2003)

    After fetching an Id and incrementing it, shouldn't you update the database table with the new Id, so the next user gets a new Id?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 85 Times in 81 Posts

    Re: ODBC problem (Excel/Access 2003)

    Yes, I would have a settings table or something to store the next ID in, but as this is a once a month type operation with the spreadsheet data then getting written back to the table anyway, I figured the timing would be OK. It would certainly be safer to have an additional table and increment the number.
    Regards,
    Rory
    Microsoft MVP - Excel.

Posting Permissions

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