Elmo_loves_you 20 Junior Poster in Training


I have to write a script within MS Access 2003 using Visual Basic for Applications to read in data from a textfile and insert it into the table. I began my programming path using dot.Net C# and started database manipulation using Sql Server. So this is kind of like a step backwards for me ... Im so used to seeing the intellisense pop up all the time to help me out.

I have previously set up a database (Insert, Update, Delete etc) using stored procedures and called them in the code accordingly. However, MS Access doesnt have stored procedures .. so I have been trying to use an ADODB.Command to insert data to the table.

I am reading data in from a specific file directory, once one file is finished reading to the end the next one is read etc... Im am pulling out the values and passing them into an array... so I want to be able to insert each individual element of the array into the database table now :)

Here is the code I have so far

Private Sub InsertAnswers(ByVal strSpecialist As String) 'For testing the rest have been removed
'Insert a new row
    On Error GoTo ErrorHandler
    'Connection Variable
    Dim strSQL As String
    Dim insert_conn As ADODB.Connection
    Set insert_conn = New ADODB.Connection
    insert_conn.Provider = "Microsoft.Jet.OLEDB.4.0"
    insert_conn.Open "C:\Users\User\Documents\Current Projects\myDatabase.mdb"    

'Command Variables
    Dim insert_Command As ADODB.Command
    Set insert_Command = New ADODB.Command
    inset_Command = "INSERT INTO FactFind(Specialist) VALUES(@specialist)"
   'Declare and Initialise Parameters
    Dim specialist_Param As ADODB.Parameter
    Set specialist_Param = New ADODB.Parameter
    specialist_Param.Value = strSpecialist
    insert_Command.Parameters.Add (specialist_Param)
End Sub

I dont mean for anyone to GIVE me the answers ... but a little advice or guidance would be great.

Thanks so much