VB-Excel-Access TransferSpreadsheet Command

Not sure if there is a way to test for transfer completion, but try using the DoEvents statement after your transfer...

Hi,
  I have tried using DoEvents statement also. Even that doesn't help. Even after the VB code executes the DoEvents command, the data transfer is still not complete.

Regards,
Subodh

Why don't you link the sheet instead of tranfer the contents?. It could be "see" like any other table in access MDB.

But, How do I link the excel sheet so as to use it as a table?

Sorry, i have Access in english:
Go to File menu, get external data, link tables...
Change file tipes combo to microsoft excel and navigate until you found target excel workbook, push "link" button.
After that, dialog box is self explantory.
It has advantages (you could use sql queries including that excel sheet!)

Greetings,

You will find Access NEVER to immediately execute ANYTHING. It's designed to execute commands when it thinks time has come. That is especially true for updating data.

There are some ways to improve updating speed, like manipulating the jet engine directly, but usually they're hard to implement.

I've succesfully tried looping with DoEvents untill the update was done, as well as doing some updates twice in a row. That however isn't always going to work.

If you need an accurate database that updates data when you want it to, don't use Access.

Imagine!

try this code... hope it helps u out!!

Option Explicit
Dim strDBName As String
Dim exl As Excel.Application
Dim eWorkBook As New Excel.Workbook
Dim eWorkSheet As New Excel.Worksheet

Private Sub cmdClose_Click()
    Unload Me
End Sub

Private Sub cmdConvert_Click()
    Dim cn As New ADODB.Connection
    Dim oSchema As ADODB.Recordset
    Dim rs As New ADODB.Recordset
    Dim intFldCnt As Integer
    Dim i As Integer
    Dim j As Integer
    Dim sngColWid As Single
   
    On Error GoTo ExcelErr
    Screen.MousePointer = vbHourglass
   
    If strDBName = "" Then
        MsgBox "Please select a database"
        Exit Sub
    End If
   
    If txtEXL.Text = "" Then
        MsgBox "Please select a name for the new spreadsheet."
        Exit Sub
    End If
    txtResults.Text = ""
    txtResults.Text = "Opening Database..." & vbCrLf
   
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBName & ";Persist Security Info=False"
    cn.Open (strDBName)
    Set oSchema = cn.OpenSchema(adSchemaTables)
   
    Set exl = New Excel.Application
    Set eWorkBook = exl.Workbooks.Add
    txtResults.Text = txtResults.Text & "Creating Workbook..." & vbCrLf
   
    Do Until oSchema.EOF
        If InStr(oSchema!table_name, "MSys") = 0 Then
            Set eWorkSheet = eWorkBook.Worksheets.Add
            txtResults.Text = txtResults.Text & "Creating Worksheet " & oSchema!table_name & "..." & vbCrLf
            If InStr(oSchema!table_name, "/") <> 0 Then
                eWorkSheet.Name = Replace(oSchema!table_name, "/", "-")
            Else
                eWorkSheet.Name = oSchema!table_name
            End If
           
            rs.Open "select * from [" & oSchema!table_name & "]", cn
            intFldCnt = rs.Fields.Count - 1
            txtResults.Text = txtResults.Text & "Adding Column Headers..." & vbCrLf
            For i = 1 To intFldCnt
                eWorkSheet.Cells(1, i) = rs.Fields(i).Name
                If TextWidth(rs.Fields(i).Name) > sngColWid Then
                    sngColWid = TextWidth(rs.Fields(i).Name)
                End If
            Next i
            eWorkSheet.Range("A1", "Z1").Font.Bold = True
            eWorkSheet.Range("A1", "Z1").Font.Underline = True
           
            j = 2
            txtResults.Text = txtResults.Text & "Adding Data from Database Table " & oSchema!table_name & "..." & vbCrLf
            Do Until rs.EOF
                For i = 1 To intFldCnt
                    eWorkSheet.Cells(j, i) = rs.Fields(i).Value
                Next i
                j = j + 1
                rs.MoveNext
            Loop
            rs.Close
            Debug.Print oSchema!table_name
        End If
        oSchema.MoveNext
    Loop
    txtResults.Text = txtResults.Text & "Done!!!!"
    eWorkBook.SaveAs txtEXL.Text
    Screen.MousePointer = vbNormal
    Exit Sub
   
ExcelErr:
    Screen.MousePointer = vbNormal
    Select Case Err.Number
        Case 1004
            Resume Next
        Case Else
            MsgBox Err.Number & vbCrLf & Err.Description
    End Select

End Sub

Private Sub cmdDB_Click()
    cdg1.Filter = "MS Access Database (*.mdb)|*.mdb"
    cdg1.ShowOpen
    strDBName = cdg1.FileName
    txtDB.Text = strDBName
End Sub

Private Sub cmdEXL_Click()
    cdg1.Filter = "MS Excel Spreadsheet (*.xls)|*.xls"
    cdg1.ShowOpen
    txtEXL.Text = cdg1.FileName

End Sub

Private Sub Form_Unload(Cancel As Integer)
   
    On Error Resume Next
   
    exl.Application.Quit
   
End Sub

Hi Anu1401,
           What you are doing in the code is to read from an access database and write it to a new excel sheet. What I am trying to do is to read from an excel sheet and write to a access database and the problem I am facing is that, it takes a long time, even after the database connection is closed, for the data to be actually inserted in the database. I already have a code almost similiar to yours reading excel cell by cell and writing to the database. But even then same sort of problem persists.

Hi Richie,
          The problem is that all this has to be done programatically and the excel sheets as well as tables can be dynamic and selected during the execution of the program. Also, tables are dynamically dropeed and recreated as per the wishes of the user. Thus there is no point in making link tables through Access mdb screen, since the underlying table itself is dynamic.

Hi SubodhShenvi,
It appears that you have forgotten this question. I will ask Community Support to close it unless you finalize it within 7 days. I will ask a Community Support Moderator to:

    Refund points and save as a 0-pt PAQ.

SubodhShenvi, Please DO NOT accept this comment as an answer.
EXPERTS: Post a comment if you are certain that an expert deserves credit.  Explain why.
==========
DanRollins -- EE database cleanup volunteer