DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

VBA Inserting data to table

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

•

Join Date: Mar 2008

Posts: 83

Reputation: Elmo_loves_you is an unknown quantity at this point

Solved Threads: 0

Elmo_loves_you Elmo_loves_you is offline

Offline

Junior Poster in Training

VBA Inserting data to table

Oct 6th, 2008

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

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
 
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
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