[RESOLVED]Create Ms Access table from Ms Excel file using Code

[vijay2482]

Hi all

I have an excel file that contains columns with datas.
i need to create a table in MS Acces with the data in the MS Excel file using code.
I dont have any clue or idea on how to do it.
Any suggestions, examples will be helpful.

Thanks in advance.

[westconn1]

you want to do the code in the excel or access applications?

[vijay2482]

Thanks for the reply.

What to write a code in Excel.

[vijay2482]

I got the below code on the internet:

Sub ADOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use

  Dim acApp As Object
 
Set acApp = CreateObject("Access.Application")
With acApp
    .opencurrentdatabase "C:\ACCESS\DataBaseName.mdb"
    With .DoCmd
        '.SetWarnings False
        .runsql "DELETE * FROM data"
       '.SetWarnings True
    End With
     'other code here perhaps
    .CloseCurrentDatabase
    .Quit
End With
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\ACCESS\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "data", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    
    ' all records in a table
  
 
Set acApp = Nothing
    r = 2 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0
    ' repeat until first empty cell in column A
        With rs
            
            .AddNew ' create a new record
            ' add values to each field in the record
            .Fields("GROUP_NUM") = Range("A" & r).Value
            .Fields("AFFECT") = Range("B" & r).Value
            .Fields("GR_ENG_CON") = Range("C" & r).Value
            .Fields("RESP_GR_EN") = Range("D" & r).Value
            .Fields("RESP_GR_2") = Range("E" & r).Value
            .Fields("RESPONSABLE") = Range("F" & r).Value
            .Fields("DES_GR_FR") = Range("G" & r).Value
            .Fields("CONTENU_FR") = Range("H" & r).Value
            .Fields("DES_GR_ANG") = Range("I" & r).Value
            .Fields("CONTENU_AN") = Range("J" & r).Value
            .Fields("INTRO") = Range("K" & r).Value
            .Fields("DATE_OUV") = Range("L" & r).Value
            .Fields("DATE_FERM") = Range("M" & r).Value
            .Fields("NUMBER") = Range("N" & r).Value
            .Fields("MAIN_GROUP") = Range("O" & r).Value
            .Fields("MAIN_FUNC") = Range("P" & r).Value
            .Fields("MAIN_GR_FR") = Range("Q" & r).Value
            .Fields("MAIN_FU_FR") = Range("R" & r).Value
            ' add more fields if necessary...
            .Update ' stores the new record
        End With
        r = r + 1 ' next row
    Loop
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

when this line is reached

.opencurrentdatabase "C:\ACCESS\DataBaseName.mdb"

the acces database is opened.
I dont want to open it visually. How to clear the contents without opening the database on the screen?

Thanks in advance

[westconn1]

I dont want to open it visually. How to clear the contents without opening the database on the screen?

instead of creating an instance of access, just add a reference to ADO (activex data objects), you can the open the database and create recordset, read, write and delete data with out any window visible to the user

this part of the code from above should open a recordset from a table called data in the database

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=C:\ACCESS\DataBaseName.mdb;"
    ' open a recordset
    Set rs = New ADODB.Recordset
    rs.Open "data", cn, adOpenKeyset, adLockOptimistic, adCmdTable

you may find that it is better not to be using adcmdtable, change database and table names to suit

you can either loop through the records and delete as required, or use some sql statement to delete all records
i am very rusty on database

[vijay2482]

how to use a loop or query in the existing code.

[westconn1]

something like

do until eof(rs)
    ' do stuff
    rs.movenext
loop

or to delete all records try
rs.delete
not sure if that is correct
with a query you can delete records based on whatever criteria you set, but i have not really done that, search in the database forum if you want more info on SQL
delete * from data where somefield = ' test'
the sql can get very much more complex

[vijay2482]

Thanks to all who replied.
The code I'm using now.

Option Explicit
 
Private Sub Export()
 
  Dim dbData As DAO.Database
  Dim rsData As DAO.Recordset
  
  Dim iLastRow As Long
  Dim iRow As Long
  
  Set dbData = DAO.OpenDatabase("C:\ACCESS\DataBaseName.mdb")
  
  ' delete the table in case it already exists
  On Error Resume Next
  dbData.Execute "DROP TABLE data;"
  On Error GoTo 0
  
  dbData.Execute "CREATE TABLE data ( " _
     & "[GROUP_NUM] Text(50), " _
     & "[AFFECT] Text(50)," _
     & "[GR_ENG_CON] Text(50)," _
     & "[RESP_GR_EN] Text(50)," _
     & "[RESP_GR_2] Text(50), " _
     & "[RESPONSABLE] Text(50), " _
     & "[DES_GR_FR] Text(50), " _
     & "[CONTENU_FR] Text(50) , " _
     & "[DES_GR_ANG] Text(50)," _
     & "[CONTENU_AN] Text(50)," _
     & "[INTRO] Text(50)," _
     & "[DATE_OUV] Text(50), " _
     & "[DATE_FERM] Text(50), " _
     & "[NUMBER] Text(50), " _
     & "[MAIN_GROUP] Text(50), " _
     & "[MAIN_FUNC] Text(50), " _
     & "[MAIN_GR_FR]Text(50), " _
     & "[MAIN_FU_FR] Text(50) );"
  
  Set rsData = dbData.OpenRecordset("data")
  With Sheets("RespEtude_GrEng_Fr")
  
    iLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    
    For iRow = 2 To iLastRow
      rsData.AddNew
      rsData!GROUP_NUM = .Cells(iRow, 1)
      rsData!AFFECT = .Cells(iRow, 2)
      rsData!GR_ENG_CON = .Cells(iRow, 3)
      rsData!RESP_GR_EN = .Cells(iRow, 4)
      rsData!RESP_GR_2 = .Cells(iRow, 5)
      rsData!RESPONSABLE = .Cells(iRow, 6)
      rsData!DES_GR_FR = .Cells(iRow, 7)
      rsData!CONTENU_FR = .Cells(iRow, 8)
      rsData!DES_GR_ANG = .Cells(iRow, 9)
      rsData!CONTENU_AN = .Cells(iRow, 10)
      rsData!INTRO = .Cells(iRow, 11)
      rsData!DATE_OUV = .Cells(iRow, 12)
      rsData!DATE_FERM = .Cells(iRow, 13)
      rsData!Number = .Cells(iRow, 14)
      rsData!MAIN_GROUP = .Cells(iRow, 15)
      rsData!MAIN_FUNC = .Cells(iRow, 16)
       rsData!MAIN_GR_FR = .Cells(iRow, 17)
      rsData!MAIN_FU_FR = .Cells(iRow, 18)
      rsData.Update
    Next iRow
    
  End With
  
  rsData.Close
  
End Sub

Hope it will be helpful for others.

[d1g1t4l_nrg]

A much easier way to do this in msaccess is to either import or create a link.
If this is a one time thing just use the import feature.
If this is a regular import then create template file.
1.) Create a excel.xls file link the in ms access.
2.) Use that to create a make table query in this ms db with a different name.
3.) Run this query now you have the table needed with all your data.
P.S. make sure your data has formatting correct for each column of your excel file.
Also make sure to use link table manager to refresh the data if you have issues with importing across a network.

Hope this helps > Have a good one. :-)

[vijay2482]

Thanks for your suggestion.
It is also a good idea...

[westconn1]

A much easier way to do this in msaccess is to either import or create a link.

only when access is installed