vba - Import rows from delimited file (.cvs) into MS-Access table -

vba - Import rows from delimited file (.cvs) into MS-Access table -

system background: working in access 2010 user interface , microsoft sql server 2008 back-end database stored.

problem background: machine in lab outputs results in form of .csv files. these files placed in folder on server. currently, results entered manually database. goal have programme in access (vba) reads in info in files row row , inserts each row of file specific table in database. instructed ado objects accomplish this.

progress: issue lies trying read in each row of the delimited file. given reference website http://msdn.microsoft.com/en-us/library/ms974559.aspx when tried implementing illustration under subtitle so how utilize ado query text file? , quite few errors insisting methods not found @ connection.open. want obtain values @ each row , store them temporary variables pass stored procedure. stored procedure created , process insert new record created.

sidenote on code: code lower level function meaning not read in .csv files in folder @ 1 time. function given file name higher level function , file name function read in each row specified file , store in table "tblicpms". here sample of .cvs file looks when opened in excel

here code:

class="lang-vb prettyprint-override">public function importicpms(thisfilename string, thisqueueid long, batchid long, instrumentname string, techid long) on error goto handleerror const adopenstatic = 3 const adlockoptimistic = 3 const adcmdtext = &h0001 dim obj_fso object dim objconnection connection dim objrecordset recordset dim strpathtotextfile string 'test if file exists (newpath public path folder)' set obj_fso = createobject("scripting.filesystemobject") if obj_fso.fileexists(newpath & "\" & thisfilename) else err.raise vbobjecterror + 1000, "myprojectname.myobjectname", "file " & thisfilename & " " & instrumentname & " not found" 'if false error raised end if set objconnection = createobject("adodb.connection") 'create ado objects' set objrecordset = createobject("adodb.recordset") strpathtotextfile = newpath & "\" 'path folder file resides' objconnection.open "provider=sqloledb.1;data source=strpathtotextfile;extended properties=;hdr = yes;fmt = delimited" objrecordset.open "select * " & thisfilename,objconnection, adopenstatic, adlockoptimistic, adcmdtext until objrecordset.eof 'i not know wscript.echo means want save acquired values temp variables pass them stored procedure inserts them table' wscript.echo objrecordset.fields.item("sample name") wscript.echo objrecordset.fields.item("date , time acquired") wscript.echo objrecordset.fields.item("element total name") wscript.echo objrecordset.fields.item("concentration") wscript.echo objrecordset.fields.item("units") 'code insert rows table go here' 'code clear out local objects go here' objrecordset.movenext loop objrecordset.close objconnection.close

i did not include rest of code nor stored procedure can if wants see them. wanted focus inserted code on part working on.

your issue methods not existing may it's not opening csv using provider. knowledge, sqloledb.1 doesn't have text reader functionality.

try this, change:

objconnection.open "provider=sqloledb.1;data source=strpathtotextfile;extended properties=;hdr = yes;fmt = delimited"

to this,

objconnection.open "provider=microsoft.jet.oledb.4.0;data source=strpathtotextfile;extended properties=""text;hdr=yes;fmt=delimited"""

update: noticed issue declarations:

dim objconnection connection dim objrecordset recordset

should work as,

dim objconnection object dim objrecordset object

vba csv access-vba ado