Insert Data in Access via VBA

[mathon]

Hello,

i try to write some data via VBA-Excel-Module in an Access-DB. Unfortunately is there an error in my VBA code and im not able to fix it.(

My method looks as follows:

Private Sub CommandButton2_Click()
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer

Cells.Clear

' Database information
DBFullName = ThisWorkbook.Path & "\PMO.mdb"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
Recordset.Open Source:="Tasks", ActiveConnection:=Connection

With Recordset
.AddNew
.Fields("ParentTask").Value = "C00T35"
.Fields("TaskID").Value = "RA-34-2345"
.Fields("Task").Value = "Maintenance"
.Fields("Org").Value = "Maintenance Team"
.Fields("TeamMember").Value = "John Hutch"
.Fields("StartDate").Value = "21.02.2009"
.Fields("DueDate").Value = "01.04.2009"
'add data for the other fields here
.Update
.Close
End With

End Sub


When I execute this code I get the following error:

Run-time error '2147217900 (80040e14)
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

In Debug Mode it stops at the following code line:

Recordset.Open Source:="Tasks", ActiveConnection:=Connection

I have tried for so long to fix the problem but I do not get it.
Has anybody an idea how the code should look like to execute correctly? :-/

matti

 

[Andrzejek]

First, Connection and Recordset are reserved words and it should not be used as variable names.

Try something like:

Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
...
Set Conn = New ADODB.Connection
Set rst = New ADODB.Recordset
...

Second, what are you trying to accomplish? Are you tryying to ADD a new record to your table in Access? If so, try simple:

strAdd = "INSERT INTO YourTableName " _
& " (ParentTask, TaskID, Task, Org, TeamMember, StartDate, DueDate) " _
& " VALUES ("C00T35", "RA-34-2345", "Maintenance", "Maintenance Team", "John Hutch", #21.02.2009#, #01.04.2009#)"

Conn.Execute strADD

Have fun.

---- Andy

 

[mathon]

Hi,

thank you very much for you answer. Yes I want to add a record in my table. the thing is i want to use

Recordset.AddNew.Fields

It should be possible with this statement or??

matti

 

[Remou]

As Andrzejek says, use suitable names. Also ensure that you use a suitable lock type and cursor.

Set rst = New ADODB.Recordset
rst.Open Source:="Tasks", ActiveConnection:=conn, CursorType:=3, LockType:=3

forum705 is one of the dedicated Access forums.

http://lessthandot.com