No Index Violation when adding data in Acccess from Excel

[Marcel]

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

 

[Joel]

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]

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]

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]

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

Marcel

 

[Joel]

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]

Joel,

How do you mean that with obj? Do you habe an code sample?

I Do it the following:
Set rst = dbsAccessData.OpenRecordset(strSQL)
If (rst.RecordCount = 1) Then

where strSql is my Select statement.

Marcel

 

[Joel]

There arre lots of ways of opening up a database and accessing the data. Too
many to specify. You are using the Jet Engine method. The Jet engine
method is the oldest method which will work with excel 97 and 2000. New
methods use DAO methods and ADO methods. It depends on the version of access
your database was created which methods can be used. Here are examples of
other methods

Sub DisplayForm()

Dim strDB as String

' Initialize string to database path.
Const strConPathToSamples = "C:\Program " _
& "Files\Microsoft Office\Office11\Samples\"

strDB = strConPathToSamples & "Northwind.mdb"
' Create new instance of Microsoft Access.
Set appAccess = _
CreateObject("Access.Application")
' Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strDB

'Below isn't required for you application

' Open Orders form.
appAccess.DoCmd.OpenForm "Orders"
End Sub

Here are more examples with SQL statements

Sub SQLX()

Dim dbsNorthwind As Database
Dim qdfTemp As QueryDef
Dim rstEmployees As Recordset

Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set qdfTemp = dbsNorthwind.CreateQueryDef("")

' Open Recordset using temporary QueryDef object and
' print report.
SQLOutput "SELECT * FROM Employees " & _
"WHERE Country = 'USA' " & _
"ORDER BY LastName", qdfTemp

' Open Recordset using temporary QueryDef object and
' print report.
SQLOutput "SELECT * FROM Employees " & _
"WHERE Country = 'UK' " & _
"ORDER BY LastName", qdfTemp

dbsNorthwind.Close

End Sub

Function SQLOutput(strSQL As String, qdfTemp As QueryDef)

Dim rstEmployees As Recordset

' Set SQL property of temporary QueryDef object and open
' a Recordset.
qdfTemp.SQL = strSQL
Set rstEmployees = qdfTemp.OpenRecordset

Debug.Print strSQL

With rstEmployees
' Enumerate Recordset.
Do While Not .EOF
Debug.Print " " & !FirstName & " " & _
!LastName & ", " & !Country
.MoveNext
Loop
.Close
End With

End Function

 

[gimme_this_gimme_that]

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.

 

[Marcel]

No,

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

Marcel

 

[Marcel]

Hello Joel,

1. In DisplayForm() you are controlling the MSAccess-Application. How can I
this way do an Insert or a Query (which is not defined in the
MSAccess(Northwind))?
Is this called Automation?

2. In SQLX() you seem to use DAO. This it at least what the Excel Object
Browser (F2) tells.

3. My Code seems to use also DAO . This it at least what the Excel Object
Browser (F2) tells

4. Now I changed my code to: (only last line, so I have no code from jet
engine)
Public Function OpenMSAccessDB()

' Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
' Set dbsAccessData = wrkJet.OpenDatabase(ActiveWorkbook.Path &
MSACCESS_DB_FILENAME)
' Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbsAccessData = OpenDatabase(ActiveWorkbook.Path &
MSACCESS_DB_FILENAME)

End Function

and I doubled my code for inserting an entry:
dbsAccessData.Execute (strSQL)
dbsAccessData.Execute (strSQL)
It is still inserting only one entry and gives no Index-violation at the
second statement!

How can I use ADO?

I have some problems to understand Jet, DAO, ADO ...Automation!

Thanks
Marcel