System background: I am working in Access 2010 with user interface and a Microsoft SQL Server 2008 back-end where the database is stored.
Problem background: A machine in my lab outputs results in the form of .csv files. These files are placed in a folder on the server. Currently, results are entered manually into the database. The goal is to have a program in access (VBA) that reads in the data in the files row by row and inserts each row of the file into a specific table in the database. I was instructed to us ADO objects to accomplish this.
Progress: My issue lies with trying to read in each row of the the delimited file. I was given a reference website http://msdn.microsoft.com/en-us/library/ms974559.aspx but when I tried implementing the example under the subtitle So How Do I Use ADO to Query a Text File? and I get quite a few errors insisting methods were not found specifically at connection.Open. I want to obtain the values at each row and store them into temporary variables to pass to a stored procedure. the stored procedure is already created and the process to insert the new record is already created.
Sidenote on code: The code is a lower level function meaning it does not read in all the .csv files in the folder at one time. The function is given a file name by a higher level function and from that file name the function will read in each row from the specified file and store it in the table "tblICPMS". Here is a sample of what the .cvs file looks like when opened in excel
Here is my code:
Public Function ImportICPMS(ThisFileName As String, ThisQueueID As Long, BatchID As Long, InstrumentName As String, TechId As Long) On Error GoTo HandleError Const adOpenStatic = 3 Const adLockOptimistic = 3 Const adCmdText = &H0001 Dim obj_fso As Object Dim objconnection As connection Dim objRecordset As Recordset Dim strpathtotextfile As String 'test if file exists (newpath is a public path to folder)' Set obj_fso = CreateObject("Scripting.FileSystemObject") If obj_fso.FileExists(NewPath & "\" & ThisFileName) Then Else Err.Raise vbObjectError + 1000, "MyProjectName.MyObjectName", "file " & ThisFileName & " for " & InstrumentName & " not found" 'if false error is raised End If Set objconnection = CreateObject("ADODB.connection") 'create ADO objects' Set objRecordset = CreateObject("ADODB.recordset") strpathtotextfile = NewPath & "\" 'path to folder where file resides' objconnection.Open "Provider=SQLOLEDB.1;Data Source=strpathtotextfile;Extended Properties=;HDR = YES;FMT = Delimited" objrecordset.Open "SELECT * FROM " & ThisFileName,objconnection, adOpenStatic, adLockOptimistic, adCmdText Do Until objRecordset.EOF 'I do not know what Wscript.Echo means but I want to save the acquired values into temp variables to pass them to a stored procedure that inserts them into the table' Wscript.Echo objRecordset.Fields.Item("Sample Name") Wscript.Echo objRecordset.Fields.Item("Date and Time Acquired") Wscript.Echo objRecordset.Fields.Item("Element Full Name") Wscript.Echo objRecordset.Fields.Item("Concentration") Wscript.Echo objRecordset.Fields.Item("Units") 'code to insert rows into table would probably go here' 'code to clear out local objects would go here' objRecordset.MoveNext Loop objRecordset.Close objconnection.Close
I did not include the rest of the code nor the stored procedure but I can if you anyone wants to see them. I wanted to just focus my inserted code on the part I was working on.