Hello,

I created a table in MSAccess with an index.
When I add rows into this table from VBA-code in Excel, I do not get any
index violation error messages in Excel and the record is not added to the
table!

Why this?

Here my code:

Public Function InsertActivity(person_id As Integer, from_time As Date,
to_time As Date, department As String, job As String, job_number As Integer)

Dim strSQL As String

On Error GoTo Err_AccessDBNotOpen
If (dbsAccessData.name = "dummy") Then ' when DB is not open -
Exception - open DB
End If

On Error GoTo Err_InsertActivity
strSQL = "Insert Into tblActivity09 (PersonID, FromTime, ToTime,
Department, Job, JobNumber) Values (" & person_id & ",'" & from_time & "','"
& _
to_time & "','" & department & "','" & job & "'," &
job_number & ")"

dbsAccessData.Execute (strSQL)

Exit Function

Err_AccessDBNotOpen:
Call OpenMSAccessDB
Resume Next

Err_InsertActivity:
MsgBox "InsertActivity()" & vbCrLf & Err.Description & "Error-Number=" &
Err.Number
Exit Function

End Function

It doesn't look like you have the datzabase object dbsAccessData defined in
the routine. It is equal to nothing and therefore you aren't even getting to
the code where you are adding the row to the database.

"Marcel" wrote:

Hello,

I created a table in MSAccess with an index.
When I add rows into this table from VBA-code in Excel, I do not get any
index violation error messages in Excel and the record is not added to the
table!

Why this?

Here my code:

Public Function InsertActivity(person_id As Integer, from_time As Date,
to_time As Date, department As String, job As String, job_number As Integer)

Dim strSQL As String

On Error GoTo Err_AccessDBNotOpen
If (dbsAccessData.name = "dummy") Then ' when DB is not open -
Exception - open DB
End If

On Error GoTo Err_InsertActivity
strSQL = "Insert Into tblActivity09 (PersonID, FromTime, ToTime,
Department, Job, JobNumber) Values (" & person_id & ",'" & from_time & "','"
& _
to_time & "','" & department & "','" & job & "'," &
job_number & ")"

dbsAccessData.Execute (strSQL)

Exit Function

Err_AccessDBNotOpen:
Call OpenMSAccessDB
Resume Next

Err_InsertActivity:
MsgBox "InsertActivity()" & vbCrLf & Err.Description & "Error-Number=" &
Err.Number
Exit Function

End Function

Hello Joel,

The code comes through where expected.
dbsAccessData is defined on top of my file!

Entries with no index violation are entered to the Access_Db!

Option Explicit

Public wrkJet As Workspace
Public dbsAccessData As Database

Public Function OpenMSAccessDB()

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsAccessData = wrkJet.OpenDatabase(ActiveWorkbook.Path &
"\fs_database.mdb")

End Function

Thanks for help
Marcel


"Joel" wrote in message
...
It doesn't look like you have the datzabase object dbsAccessData defined
in
the routine. It is equal to nothing and therefore you aren't even getting
to
the code where you are adding the row to the database.

"Marcel" wrote:

Hello,

I created a table in MSAccess with an index.
When I add rows into this table from VBA-code in Excel, I do not get any
index violation error messages in Excel and the record is not added to
the
table!

Why this?

Here my code:

Public Function InsertActivity(person_id As Integer, from_time As Date,
to_time As Date, department As String, job As String, job_number As
Integer)

Dim strSQL As String

On Error GoTo Err_AccessDBNotOpen
If (dbsAccessData.name = "dummy") Then ' when DB is not open -
Exception - open DB
End If

On Error GoTo Err_InsertActivity
strSQL = "Insert Into tblActivity09 (PersonID, FromTime, ToTime,
Department, Job, JobNumber) Values (" & person_id & ",'" & from_time &
"','"
& _
to_time & "','" & department & "','" & job & "'," &
job_number & ")"

dbsAccessData.Execute (strSQL)

Exit Function

Err_AccessDBNotOpen:
Call OpenMSAccessDB
Resume Next

Err_InsertActivity:
MsgBox "InsertActivity()" & vbCrLf & Err.Description & "Error-Number="
&
Err.Number
Exit Function

End Function

I would add a Watch on the object dbsAccessData. Highlight variable with
mouse and right click. then select Add Watch. I would put a break point on
the line where you are adding the row. then view the property
dbsAccessData.Recordcount in the watch item. then step through the add row
line by pressing F8. then check the record count to see that it increased by
1. Adding a row should increase the record count by 1.

"Marcel" wrote:

Hello Joel,

The code comes through where expected.
dbsAccessData is defined on top of my file!

Entries with no index violation are entered to the Access_Db!

Option Explicit

Public wrkJet As Workspace
Public dbsAccessData As Database

Public Function OpenMSAccessDB()

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsAccessData = wrkJet.OpenDatabase(ActiveWorkbook.Path &
"\fs_database.mdb")

End Function

Thanks for help
Marcel


"Joel" wrote in message
...
It doesn't look like you have the datzabase object dbsAccessData defined
in
the routine. It is equal to nothing and therefore you aren't even getting
to
the code where you are adding the row to the database.

"Marcel" wrote:

Hello,

I created a table in MSAccess with an index.
When I add rows into this table from VBA-code in Excel, I do not get any
index violation error messages in Excel and the record is not added to
the
table!

Why this?

Here my code:

Public Function InsertActivity(person_id As Integer, from_time As Date,
to_time As Date, department As String, job As String, job_number As
Integer)

Dim strSQL As String

On Error GoTo Err_AccessDBNotOpen
If (dbsAccessData.name = "dummy") Then ' when DB is not open -
Exception - open DB
End If

On Error GoTo Err_InsertActivity
strSQL = "Insert Into tblActivity09 (PersonID, FromTime, ToTime,
Department, Job, JobNumber) Values (" & person_id & ",'" & from_time &
"','"
& _
to_time & "','" & department & "','" & job & "'," &
job_number & ")"

dbsAccessData.Execute (strSQL)

Exit Function

Err_AccessDBNotOpen:
Call OpenMSAccessDB
Resume Next

Err_InsertActivity:
MsgBox "InsertActivity()" & vbCrLf & Err.Description & "Error-Number="
&
Err.Number
Exit Function

End Function

Hello Joel,,
I am doing now a select before the insert, to be shure that the entry
entries in to the table.

Marcel

"Joel" wrote in message
...
I would add a Watch on the object dbsAccessData. Highlight variable with
mouse and right click. then select Add Watch. I would put a break point
on
the line where you are adding the row. then view the property
dbsAccessData.Recordcount in the watch item. then step through the add
row
line by pressing F8. then check the record count to see that it increased
by
1. Adding a row should increase the record count by 1.

"Marcel" wrote:

Hello Joel,

The code comes through where expected.
dbsAccessData is defined on top of my file!

Entries with no index violation are entered to the Access_Db!

Option Explicit

Public wrkJet As Workspace
Public dbsAccessData As Database

Public Function OpenMSAccessDB()

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsAccessData = wrkJet.OpenDatabase(ActiveWorkbook.Path &
"\fs_database.mdb")

End Function

Thanks for help
Marcel


"Joel" wrote in message
...
It doesn't look like you have the datzabase object dbsAccessData
defined
in
the routine. It is equal to nothing and therefore you aren't even
getting
to
the code where you are adding the row to the database.

"Marcel" wrote:

Hello,

I created a table in MSAccess with an index.
When I add rows into this table from VBA-code in Excel, I do not get
any
index violation error messages in Excel and the record is not added to
the
table!

Why this?

Here my code:

Public Function InsertActivity(person_id As Integer, from_time As
Date,
to_time As Date, department As String, job As String, job_number As
Integer)

Dim strSQL As String

On Error GoTo Err_AccessDBNotOpen
If (dbsAccessData.name = "dummy") Then ' when DB is not open -
Exception - open DB
End If

On Error GoTo Err_InsertActivity
strSQL = "Insert Into tblActivity09 (PersonID, FromTime, ToTime,
Department, Job, JobNumber) Values (" & person_id & ",'" & from_time &
"','"
& _
to_time & "','" & department & "','" & job & "'," &
job_number & ")"

dbsAccessData.Execute (strSQL)

Exit Function

Err_AccessDBNotOpen:
Call OpenMSAccessDB
Resume Next

Err_InsertActivity:
MsgBox "InsertActivity()" & vbCrLf & Err.Description &
"Error-Number="
&
Err.Number
Exit Function

End Function

I don't like using select. I prefer to set a variable to the obj then
checking if the object equals nothing. By selecting an object that doesn't
exist will cause an error then you need an ON Error statement to handle the
error.

"Marcel" wrote:

Hello Joel,,
I am doing now a select before the insert, to be shure that the entry
entries in to the table.

Marcel

"Joel" wrote in message
...
I would add a Watch on the object dbsAccessData. Highlight variable with
mouse and right click. then select Add Watch. I would put a break point
on
the line where you are adding the row. then view the property
dbsAccessData.Recordcount in the watch item. then step through the add
row
line by pressing F8. then check the record count to see that it increased
by
1. Adding a row should increase the record count by 1.

"Marcel" wrote:

Hello Joel,

The code comes through where expected.
dbsAccessData is defined on top of my file!

Entries with no index violation are entered to the Access_Db!

Option Explicit

Public wrkJet As Workspace
Public dbsAccessData As Database

Public Function OpenMSAccessDB()

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsAccessData = wrkJet.OpenDatabase(ActiveWorkbook.Path &
"\fs_database.mdb")

End Function

Thanks for help
Marcel


"Joel" wrote in message
...
It doesn't look like you have the datzabase object dbsAccessData
defined
in
the routine. It is equal to nothing and therefore you aren't even
getting
to
the code where you are adding the row to the database.

"Marcel" wrote:

Hello,

I created a table in MSAccess with an index.
When I add rows into this table from VBA-code in Excel, I do not get
any
index violation error messages in Excel and the record is not added to
the
table!

Why this?

Here my code:

Public Function InsertActivity(person_id As Integer, from_time As
Date,
to_time As Date, department As String, job As String, job_number As
Integer)

Dim strSQL As String

On Error GoTo Err_AccessDBNotOpen
If (dbsAccessData.name = "dummy") Then ' when DB is not open -
Exception - open DB
End If

On Error GoTo Err_InsertActivity
strSQL = "Insert Into tblActivity09 (PersonID, FromTime, ToTime,
Department, Job, JobNumber) Values (" & person_id & ",'" & from_time &
"','"
& _
to_time & "','" & department & "','" & job & "'," &
job_number & ")"

dbsAccessData.Execute (strSQL)

Exit Function

Err_AccessDBNotOpen:
Call OpenMSAccessDB
Resume Next

Err_InsertActivity:
MsgBox "InsertActivity()" & vbCrLf & Err.Description &
"Error-Number="
&
Err.Number
Exit Function

End Function

Get rid of the error handling stuff. Nothing is happening because an
error is occuring and resume next executes until the code bails.

The problem might have to do with the data - something totally
unrelated to the index you created. Like there being a blank personId.

No,

When Error in Inserting, the code would jump to Err_InsertActivity and open
a MsgBox !

Marcel

wrote in message
...
Get rid of the error handling stuff. Nothing is happening because an
error is occuring and resume next executes until the code bails.

The problem might have to do with the data - something totally
unrelated to the index you created. Like there being a blank personId.