dCount dilemma...a mystery for the ages

[OhioSteve]

A) My db has a table called single that stores metadata. single always has one row. Each field in it functions like a global variable.

B) I have a function in a module. The purpose of the function is to determine whether a connection with our sql server is available. This function has three commands.

The first command deletes all of the rows in a test table.

The second command calls a DTS package in SQL Server. That package should put one row into the table.

The third command runs a query that uses dcount() to determine whether the table contains records. The third command's syntax is "DoCmd.OpenQuery ("setConnected")".

Here is the query's syntax:
UPDATE [single] SET [single].connected = IIf(DCount("pk","checkConnection")=1,True,False);

notek is an autonumber field. so it can never be null.

C) If I comment out the third line, run the function, and then run the update query manually, everything works PERFECTLY.

D) If I uncomment the third line and run the function, dcount does not function properly. It returns zero even if checkConnection contains a record. This is inexplicable and terribly frustrating.

 

[Zion7]

OS, not sure if I'm correct here to assume it's a syntax, variable issue, but possibly this,

Dim blnConnected As boolean

blnConnected = IIf(DCount("pk","checkConnection")=1,True,False)

"UPDATE [single] SET [single].connected = " & blnConnected

 

[Frink]

Hallo,

The UPDATE SQL keyword is used to change existing data. Use INSERT INTO to add rows.
At least that's how I understood it to work. Maybe MS assumes that if you use UPDATE on an empty table you want to add a row.

I don't think that's the problem you're reporting, but it's probably not helping.
Why not use the SQL EXISTS command to check for a record?

- Frink

 

[jsteph]

Just a thought...your query "setConnected" isn't open for viewing is it? The OpenQuery Method won't run if so.
--Jim

 

[RoyVidar]

I suspect it's som race condition thingie - the table is probably not populated when you run your query.

When doing something on the SQL server, and something on the table from within Access, I wouldn't know how to handle it, except try a DoEvents (probably won't work?) or add some delay/time - use the Sleep API for instance?

There's supposed to be some ways of working with this, but I wouldn't know how it fits with DTS - check out KB article 200300 at Microsoft.

Roy-Vidar

 

[OhioSteve]

It was a dumb syntax error that I discovered the next day.