[Excel VBA]Select range from Excel and insert into Access database -

[Excel VBA]Select range from Excel and insert into Access database -

i have table constructed in excel pasted later(after manipulation) access table. works copy-paste in windows, want automate process. talking big number of columns(a:ay) , big number of records(10.000). i've seen done ado recordset row row. there mass method?

`public sub test() dim conndb new adodb.connection 'dim rng range strdbname = "kiian.mdb" strmypath = "d:\work\kiian" strdb = strmypath & "\" & strdbname dim xlxml object dim adorecordset object dim rng range 'this trick found on boards create recordset range 'without connection, creates object, not recordset sheets("mdb all").activate set rng = range("a1:ay3") set adorecordset = createobject("adodb.recordset") set xlxml = createobject("msxml2.domdocument") xlxml.loadxml rng.value(xlrangevaluemspersistxml) adorecordset.open xlxml conndb.open connectionstring:="provider = microsoft.ace.oledb.12.0; info source=" & strdb 'delete records in pvanag table: strsql = "insert pvanag select * adorecordset" 'conndb.execute commandtext:=strsql conndb.execute strsql, nr msgbox (nr) 'sheets("mdb_all").range("a1:ay3").copyfromrecordset rstdata 'close objects conndb.close 'destroy variables set adorecset = nil set conndb = nil end sub

` basically, need this: specify range sheet , block insert access table. help appreciated. cheers

a method (that haven't tested) utilize access objects in excel project. fast import excel files in access docmd.transferspreadsheet. can have utilize referencing microsoft access object xx.0 (xx.0 version installed in system). then, in code have like:

dim db access.application set db = new access.application db.opencurrentdatabase filepath="d:\database1.accdb", exclusive:=true db.docmd.transferspreadsheet 'all_params_here db.closecurrentdatabase db.quit acquitsaveall

details function docmd.transferspreadsheet

excel-vba