Results 1 to 3 of 3
  1. #1
    jjfaith is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    5

    Error Opening MS access table from Excel ADO

    I am using MS Excel 2010 to store data in MS Access 2010 table WorkingData. This routine worked fine previously but now it doesn't. I have tried with other table but the error seem to be table specific. Can anyone tell me what may be causing the error. I have dropped and created the table but that does not help.

    Dim cnn As ADODB.Connection
    Dim MyConn
    Dim rst2 As ADODB.Recordset
    Dim i As Long, j As Long, r As Long
    Dim Rw As Long
    Dim cmd As ADODB.Command
    Dim dtmDate As Date
    Dim nPeriod As Integer
    ....


    'Give your Excel Sheet data Range
    Set cnn = New ADODB.Connection
    MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB


    With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open MyConn


    End With


    Set rst2 = New ADODB.Recordset
    rst2.CursorLocation = adUseServer


    rst2.Open Source:="WorkingData", ActiveConnection:=cnn, _
    CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
    Options:=adCmdTable

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I use this.
    you dont have to set rst = new rst. It does that at creation...

    Code:
    dim sSql as string
    dim rst as ADOdb.recordset
    
    sSql = "select * from table"
    Set gConn = New ADODB.Connection
    gConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sDbPath & ";"
    rst.Open sSql, gConn, adOpenDynamic

  3. #3
    jjfaith is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    5
    Thank you for the response. I actually created a new subroutine and started from scratch and now it seems to be working. Still Not sure what happened

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Opening Excel From Access
    By jimmy.jowers08 in forum Modules
    Replies: 15
    Last Post: 07-15-2015, 03:01 PM
  2. Excel Not Opening from Access
    By TinaH in forum Access
    Replies: 1
    Last Post: 01-08-2015, 03:33 PM
  3. Opening Excel in Access 2013
    By etorasso in forum Access
    Replies: 0
    Last Post: 12-18-2014, 03:10 PM
  4. Opening Excel Workbook From Access with VBA
    By kestefon in forum Access
    Replies: 4
    Last Post: 01-31-2014, 06:51 PM
  5. Replies: 2
    Last Post: 12-19-2009, 09:38 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums