Heya
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
Elmo