Using Excel VBA to Export data to Ms.Access Table

[ahmed_one]

I am current using following code to export data from worksheet to Ms.Access database, the code is looping through each row and insert data to Ms.Access Table.

Public Sub TransData()

Application.ScreenUpdating = False
Application.EnableAnimations = False
Application.EnableEvents = False
Application.DisplayAlerts = False


ActiveWorkbook.Worksheets("Folio_Data_original").Activate


Call MakeConnection("fdMasterTemp")

For i = 1 To rcount - 1
rs.AddNew
rs.Fields("fdName") = Cells(i + 1, 1).Value
rs.Fields("fdDate") = Cells(i + 1, 2).Value
rs.Update

Next i


Call CloseConnection


Application.ScreenUpdating = True
Application.EnableAnimations = True
Application.EnableEvents = True
Application.DisplayAlerts = True


End Sub


Public Function MakeConnection(TableName As String) As Boolean
'*********Routine to establish connection with database

Dim DBFullName As String
Dim cs As String

DBFullName = Application.ActiveWorkbook.Path & "\FDData.mdb"

cs = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

Set cn = CreateObject("ADODB.Connection")

If Not (cn.State = adStateOpen) Then
cn.Open cs
End If

Set rs = CreateObject("ADODB.Recordset")

If Not (rs.State = adStateOpen) Then

rs.Open TableName, cn, adOpenKeyset, adLockOptimistic

End If

End Function




Public Function CloseConnection() As Boolean
'*********Routine to close connection with database

On Error Resume Next
If Not rs Is Nothing Then
rs.Close
End If


If Not cn Is Nothing Then
cn.Close

End If
CloseConnection = True
Exit Function


End Function

Above code works fine for few hundred lines of records, but apparently it will be more data to export, Like 25000 records, is it possible to export without looping through all records and just one SQL INSERT statement to bulk insert all data to Ms.Access Table in one go?

Any help will be much appreciated.

Thanks

Ahmed

 

[Ruddles]

Well done at cracking the problem and thanks for posting your code. I've tried it and added it to my library.

 

[ahmed_one]

Well done at cracking the problem and thanks for posting your code. I've tried it and added it to my library.

You are welcome...I am glad this code has been starting to help someone...

Best regards

Ahmed

 

[lion22x]

Hello Ahmed,

I think I have an similar issue.
I want to update a access database every month or so with data from Excel.
My source consists of a big table where any information is given that will be needed is given. But I have to seperate the Information to several tables so that it fits to the design of the database. This part I actually can do in Excel. But nevertheless afterwards I have to update the tables in my database.
I actually knew that I could use linked tables but, than I define Primary Keys and I am not sure if the queries will Work than correct.
So I wanted to update the tables with new information.
I have realy no idea how this is gone to be done. It is also not easy to understand your code for me without comments.
I would be very thankfull if you could explain me your code

Best Regards
lion22x

 

[ahmed_one]

Hello Ahmed,

I think I have an similar issue.
I want to update a access database every month or so with data from Excel.
My source consists of a big table where any information is given that will be needed is given. But I have to seperate the Information to several tables so that it fits to the design of the database. This part I actually can do in Excel. But nevertheless afterwards I have to update the tables in my database.
I actually knew that I could use linked tables but, than I define Primary Keys and I am not sure if the queries will Work than correct.
So I wanted to update the tables with new information.
I have realy no idea how this is gone to be done. It is also not easy to understand your code for me without comments.
I would be very thankfull if you could explain me your code

Best Regards
lion22x

Dear lion22x

thanks for posting on this thread...

it's a fairly simple code to insert data from Excel sheet to Ms.Access:

Public Sub DoTrans()

//I am using late binding for creating objects, this code will run on any version of Excel
Set cn = CreateObject("ADODB.Connection")

//This line will set the path of Ms.Access database, in this case it is assumed that Ms.Access DB will be in same folder as in Excel File.
dbPath = Application.ActiveWorkbook.Path & "\FDData.mdb"

// This line will set the path of Active workbook
dbWb = Application.ActiveWorkbook.FullName

// Get the Active sheet name, so that we can correctly Export data using this variable
dbWs = Application.ActiveSheet.Name

// Set the connection, we are creating Excel workbook connection
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath

//Another variable to hold worksheet name, but in this case it will //be in a format that is required to pass in open connection //method, which execute the SQL statement in next line
dsh = "[" & Application.ActiveSheet.Name & "$]"

// Open the connection
cn.Open scn

//Create SQL statement using proper sheet name dsh, created //before opening connection

ssql = "INSERT INTO fdFolio ([fdName], [fdOne], [fdTwo]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh


//Run the SQL
cn.Execute ssql


End Sub


You can use a for loop to run the above procedure for multiple sheets to export data to Ms.Access Database.

Let me know if this helps

regards

Ahmed

 

[lion22x]

You can use a for loop to run the above procedure for multiple sheets to export data to Ms.Access Database.

Let me know if this helps

regards

Ahmed

Hello Ahmed it helped me a lot!!!!! Thank you!!!!
How I should use the code in a loop?

Regards

Lion22x

 

[ahmed_one]

Hello Ahmed it helped me a lot!!!!! Thank you!!!!
How I should use the code in a loop?

Regards

Lion22x

Dear Lion22x,

You can use something like this:

Dim shCount As Integer
Dim i As Integer

' Set shCount equal to the number of worksheets in the active
' workbook.
shCount = ActiveWorkbook.Worksheets.Count

' Begin the loop.
For i = 1 To shCount
//Use DoTrans Proc here, you need to modify the Proc so that you //can pass Sheet
//name as aurgument for export

Call DoTrans("SheetName")

Next i

Regards

Ahmed

 

[jolielle]

Hi Ahmed thx so much for sharing the code. I tried it and it works for importing whole worksheet. however, how about importing named ranged in excel? for example assume my name range in excel is called "range1"and I tried below but it doesn't work.

ssql = "INSERT INTO " & tableName

ssql = ssql & " SELECT * FROM [range1] IN [" & dbwb & "; Excel 8.0;HDR=YES]"

 

[Fazza]

hi, Ahmed

Your posts have reminded me of some earlier threads.

For example post #5 in thread http://www.mrexcel.com/forum/excel-questions/332472-excel-acess-via-dao.html

I don't recall the details, but I found this imperfect sometimes and decided against using it.

Post #3 in this later thread http://www.mrexcel.com/forum/excel-questions/597499-send-rst-access-table.html was more robust and I think what I still use. (Haven't got samples with me to check as not in office.) As you can see the earlier post simply references the whole sheet and the later code has a extra steps via an array and looping one record at a time.

regards

 

[ahmed_one]

Hi Ahmed thx so much for sharing the code. I tried it and it works for importing whole worksheet. however, how about importing named ranged in excel? for example assume my name range in excel is called "range1"and I tried below but it doesn't work.

ssql = "INSERT INTO " & tableName

ssql = ssql & " SELECT * FROM [range1] IN [" & dbwb & "; Excel 8.0;HDR=YES]"

Dear jolielle,

At present I haven't able to use named range, but as soon as I succeed I will post the modified version of code..

Regards

Ahmed

 

[ahmed_one]

hi, Ahmed

Your posts have reminded me of some earlier threads.

For example post #5 in thread http://www.mrexcel.com/forum/excel-questions/332472-excel-acess-via-dao.html

I don't recall the details, but I found this imperfect sometimes and decided against using it.

Post #3 in this later thread http://www.mrexcel.com/forum/excel-questions/597499-send-rst-access-table.html was more robust and I think what I still use. (Haven't got samples with me to check as not in office.) As you can see the earlier post simply references the whole sheet and the later code has a extra steps via an array and looping one record at a time.

regards

Dear Faaza,

Thanks for pointing out a useful link, it help me also to modify code to add Named Range which was requested by jolielle... I will post the code...

Regards

Ahmed