Append excel table into Access table on weekly or monthly basis

[praloy sangm]

Hey Guys,

I am an intermediate VBA programer in Excel. I do have knowledge about access tables and queries and I do know that it is easier to import excel tables from Access using import function.
Is there any efficient way to append the tables from Excel into existing access table with the same table format (considering that access and excel table is a mirror to each other) via VBA in excel on monthly or weekly basis?

Kindly mention the VBA Objects to be used(Tools/VBA Reference) in vb editor if it has to be used.

Regards and thanks in advance.

Praloy

[Kenneth Hobs]

Here is one method that is fairly simple.

Sub demo()
Dim objRS As Object, nwindPath As String
Set objRS = CreateObject("ADODB.Recordset")
nwindPath = ThisWorkbook.Path & "\nwind.mdb"

Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"

objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath

Set objRS = Nothing
End Sub

[praloy sangm]

Hello,

Thanks for responding. I created a table with same name field(without spaces in access) and then try to execute this code but was showing the error as could not find the files. Moreover the code is only for two rows, if i have more than two rows, can i loop through the range?

I checked on vba object "Microsoft ADO ext 2.8 for DDL and Security" but would not still work.

Am i missing something?

Regards,
Praloy

[Kenneth Hobs]

Without seeing your code and possibly the files, I have no way of knowing what went wrong.

CreateObject is a late binding method so there is no need to select the ADO objects from VBE's Tools > References. However, that object(s) must be installed and checking the references hurts nothing. I have used both the late and early binding methods on some projects. Some programmers do the development using early binding methods but convert it to late binding for the production version.

Try something simple first, like I did. You can easily download the northwind database or nwind.dbd from Microsoft or one of many sites. You can then easily set a range and name it like I did. The first row must be your fieldnames and they must match the fieldnames in the MDB file. They need not be in the same order though.

As for the scheduled times, use Windows and have it launch your Excel file at the interval that you need. Set your code to run in the Open event for that workbook.

[praloy sangm]

I will do that for sure. Thank for responding to my query. Sorry to respond late and i have been busy. would be back with result in some time. Till then take care.