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




"Joel" wrote in message
...
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




"Marcel" wrote:

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" wrote in message
...
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