Excel to Acess via DAO

[gapa]

Hi,

wondering if someone could help me out here. I have copied this piece of code that will enable you to copy Excel data in Access. The problem is that it is copying cell by cell. Is there a way to modify it so that it can copy the whole row (row 2 to blank) or whole column or even better then whole sheet ?

thanks

Paul

the code is attached below

'------------------
Sub DAOFromExcelToAccess()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\FolderName\DataBaseName.mdb")
' open the database
Set rs = db.OpenRecordset("TableName", dbOpenTable)
' get all records in a table
r = 3 ' 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("FieldName1") = Range("A" & r).Value
.Fields("FieldName2") = Range("B" & r).Value
.Fields("FieldNameN") = Range("C" & 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
db.Close
Set db = Nothing
End Sub

 

[Fazza]

For info, there is some good information here http://www.xtremevbtalk.com/showthread.php?t=217783

Untested quick examples of using the main three ADO objects below - have assumed SQL and connection strings. These are all late bound. HTH, F

Sub ADO_Recordset_Object()
 
  Dim objRS As Object
 
  Set objRS = CreateObject("ADODB.Recordset")
  objRS.Open strSQL, strConnection
  Set objRS = Nothing
End Sub
 
Sub ADO_Connection_Object()
 
  Dim objConn As Object
 
  Set objConn = CreateObject("ADODB.Conenction")
  With objConn
    .Open strConnection
    .Execute strSQL
    .Close
  End With
  Set objConn = Nothing
End Sub
 
Sub ADO_Command_Object()
 
  Dim objCommand As Object
 
  Set objCommand = CreateObject("ADODB.Command")
 
  With objCommand
    .activeconnection = strConnection
    .CommandText = strSQL
    .Execute
    .activeconnection = Nothing
  End With
  Set objCommand = Nothing
End Sub

 

[Fazza]

Another great site: http://excelkb.com/ (I lost it from my favourites but found it in a back up.) There is some good info so I thought it re-visiting this thread to add the link. HTH, Fazza

 

[Fazza]

Some other links that might be helpful. F

http://msdn.microsoft.com/en-us/library/ms675532(VS.85).aspx

http://support.microsoft.com/kb/257819

http://www.connectionstrings.com/default.aspx

http://www.dailydoseofexcel.com/archives/2004/06/03/external-data-mixed-data-types/

http://support.microsoft.com/kb/246335

http://support.microsoft.com/default.aspx?scid=kb;en-us;q319998

 

[rasciodc]

Hi,

For a worksheet named Sheet1 feeding into table 'MyTable' in 'D:\test\test_db.mdb', see below. Late bound ADO.

If you want, the sheet reference can be to a specific range or a named range. Assumption I've made is that the data has headers in row 1 of the worksheet and data below that.

HTH, Fazza

Sub demo()
  Dim objRS As Object
  Set objRS = CreateObject("ADODB.Recordset")
 
  objRS.Open "INSERT INTO MyTable SELECT * FROM [Sheet1$] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "D:\test\test_db.mdb"
 
  Set objRS = Nothing
End Sub

Apparently this is limited to 65536 rows (older versions of excel). Is there a way to modify it to allow for more rows???

 

[Fazza]

Use a connection string for a newer version. So not Excel 8.0

This site has info on connection strings
https://www.connectionstrings.com/

OK?

If that doesn't work, please advise. Maybe the recordset can be loaded differently. Such as the copyfromrecordset method of a range. and there is MaxRows parameter. Just need to check details as I don't recall its limits.

 

[rasciodc]

Use a connection string for a newer version. So not Excel 8.0

This site has info on connection strings
https://www.connectionstrings.com/

OK?

If that doesn't work, please advise. Maybe the recordset can be loaded differently. Such as the copyfromrecordset method of a range. and there is MaxRows parameter. Just need to check details as I don't recall its limits.

I'm using ACE 12.0 and Excel 12.0. Still can only export 65536 rows. Trying to use ACE 16.0 gives me the "not installable ISAM" error...

 

[Fazza]

I'm using ACE 12.0 and Excel 12.0. Still can only export 65536 rows. Trying to use ACE 16.0 gives me the "not installable ISAM" error...

Can you explain what you're doing? I don't understand if you're going from Excel to Access or Access to Excel; where the code is. And the software versions. And the connection string. I may not be able to match your environment for testing, btw.

Have you only tried ADO? Or also DAO?

Best to post the full code too for anyone to be able to help. If I can't help you might be best to post in a new thread - this one is ~10 years old.

regards

 

[rasciodc]

Can you explain what you're doing? I don't understand if you're going from Excel to Access or Access to Excel; where the code is. And the software versions. And the connection string. I may not be able to match your environment for testing, btw.

Have you only tried ADO? Or also DAO?

Best to post the full code too for anyone to be able to help. If I can't help you might be best to post in a new thread - this one is ~10 years old.

regards

I'm exporting data from Excel 2016 to Access 2016. I'm not posting full code as it is long, but here is the related part:

Dim cnn As Object
Dim workbookname As String
Set cnn = CreateObject("ADODB.Connection")
cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=K:\Control\DB.accdb"
cnn.Open cnn

sqlstring = "INSERT INTO Price_Table([Date],[CL]) SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=K:\Control\Adjust.xlsb].[Clip$A:B] WHERE Data=#01/01/2017#;"
cnn.Execute sqlstring
cnn.Close

The bolded part is where I use SQL to select data from the columns A:B in the sheet "Clip". It reads only data between rows 1 and 65535. If the data is below data, nothing is updated to access.

I tried to force it to read all rows by having [Clip$A1:B700000] for exaxmple. In that case I get error "The Microsoft Access database engine could not find the object Clip$A1:B700000. Make sure the object exists and that you spell its name and the path name correctly. If Clip$A1:B700000 is not a local object, check your network connection or contact the server administrator."

Am I doing something wrong?

 

[Fazza]

First off, I don't use that approach - I have posted (at the end of the message) a link to some code that uses an approach that I've found to be more robust.

However, commenting on the specific code you've posted. The syntax is a little different to the previous.

It was SELECT * FROM [Sheet1$] IN '" & ThisWorkbook.FullName
And you have SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=K:\Control\Adjust.xlsb].[Clip$A:B]

I understand you've got it working for up to 65,536 rows so that means it is good. I can't help on that 65,536 row limit sorry.

Here is an alternative approach https://www.mrexcel.com/forum/excel-questions/679750-transfer-spread-sheet-access.html

 

[Fazza]

PS. Note that linked thread uses a connection string for earlier Access version. Guess you'll be OK with changing to ACE 12.0

Just noticed I have some code in a recent project. Been using it for a while

Sub ADO_ExcelToAccess(ByVal DB_Path As String, ByVal DB_TableName As String, ByVal SourceWorksheetName As String)
1000
1001     Dim r As Long, i As Long
1002     Dim cn As Object
1003     Dim rs As Object
1004     Dim ar As Variant
1005
1006     ar = Worksheets(SourceWorksheetName).Range("A1").CurrentRegion.Value
1007
1008     Set cn = CreateObject("ADODB.Connection")
1009     cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DB_Path & ";"
1010
1011     Set rs = CreateObject("ADODB.Recordset")
1012     rs.Open DB_TableName, cn, 1, 3, 2
1013
1014     For r = 2 To UBound(ar, 1)
1015         With rs
1016             .AddNew
1017             For i = LBound(ar, 2) To UBound(ar, 2)
1018                 .Fields(ar(1, i)) = ar(r, i)
1019             Next i
1020             .Update
1021         End With
1022     Next r
1023
1024     rs.Close
1025     Set rs = Nothing
1026     cn.Close
1027     Set cn = Nothing
1028     Erase ar
End Sub