Excel data to Access database

[rowena]

Hi!

I will do a program that will get the data from an excel file and it will be loaded to Access database. Do you have any idea on how to do it? If yes, please advise me ASAP.

Thanks a lot!!!

[Lafor]

Could be wrong

But seems like U need an import function to get data from Excel intoAccess. Am I correct?

[rowena]

Lafor, thanks for your immediate response. Yes, I might need it... Actually, I still have no idea on how to do it that's why I am not even aware of what I will need.

Thanks again.

[Lafor]

Row!
Help regarding this in ACCESS is pretty good (I got the
following from the help files)
Check it out if u get a chance

(I vaguely remember doing something like this 3 years ago)


2 Do one of the following:

· To import, export, or link data between the current Microsoft Access database and another database, use the TransferDatabase method to carry out the TransferDatabase action in the procedure.

· To import, export, or link data between the current Microsoft Access database and a spreadsheet file, use the TransferSpreadsheet method to carry out the TransferSpreadsheet action in the procedure.

· To import, export, or link data between the current Microsoft Access database and a text file, use the TransferText method to carry out the TransferText action in the procedure.

[quadoc]

You can use ADO to read Excel straight into ACCESS. There is a problem, if your Excel column is numeric the ADO returns nulls in the recordset.

[Lafor]

Quad...

Very good.. Would u have a quick example

If not, ignore this

Thanks

[quadoc]

Here is the code:

Private Sub Command1_Click()
'uses ADO 2.x, ADO 2.x for DDL and Security
    Dim ax As ADOX.Catalog
    Dim cn As ADODB.Connection
    Dim rs As Recordset
        
    Dim i As Long
    Dim j As Integer
    
    Dim strTemp As String
    
    Dim tbl As ADOX.Table
   
    Set cn = New Connection
    Set ax = New Catalog
    
    'open connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Zip\ADO\Xls2Mdb\004.xls;Extended Properties=Excel 8.0"
 
    ax.ActiveConnection = cn
    
    'loop thru tables
    For Each tbl In ax.Tables
    
        Set rs = cn.Execute("Select * from [" & tbl.Name & "]")
        
        'display all table names
        Debug.Print "TABLE:  " & tbl.Name & vbCrLf & "========================" & vbCrLf
        
        'display all field names
        For i = 0 To 7  'rs.Fields.Count - 1
            strTemp = strTemp & rs.Fields(i).Name & vbTab
        Next i
        
        Debug.Print vbTab & vbTab & strTemp
        strTemp = ""
        
        'display all values
        Do Until rs.EOF = True
            j = j + 1
            For i = 0 To 7   'rs.Fields.Count - 1
                strTemp = strTemp & rs.Fields(i).Value & vbTab & vbTab
               
            Next i
            
            strTemp = strTemp & vbCrLf
            rs.MoveNext
        Loop
        
        Debug.Print strTemp
    Next tbl

End Sub

[rowena]

Hi! I tried using the "TransferSpreadSheet" function in transferring my excel file to Access database. However, during execution of my program, this error occurs: "Runtime error -2147417851(800010105) Method 'TransferSpreadSheet' of object 'IDoCmd' failed". What's wrong with my script? I just followed what Lafor said.

I also tried using the script that Quad posted however, this is the error message that I got: "Runtime error -2147467259(80004005) Method 'Couldn't find installable ISAM".

Please help... thanks a lot!!!

[Lafor]

I am not sure what your line looks like

should somewhat be similar to this...
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "mytable", "c:\uotest\ExcelTest", False

also ensure that the destination table must contain fields similar to those of Excel