Listing the number of records in each table of a database.

[Jamie Collins]

And if you get into the habit of using SQL instead of using VBA code to
cursor through ADO or DAO recordsets, the eventual move to a *real* RDBMS
will be even happier.
Amen.

Oh, I agree with you. If I could de-commercialize Christmas, I would. But
the kids are young enough that presents are extremely important to them and
their peers.

I know what you mean: it's only September I've already been presented
with a (verbal) xmas list featuring the aforementioned plastic doll!

Jamie.

--

 

[Jamie Collins]

get into the habit of using SQL instead of using VBA code to
cursor through ADO or DAO recordsets

Indeed. This is the SQL/set-based (good) mindset verses the
cursor/procedural mindset (bad).

I realise I still have some way to go because, although I no longer do
the cursor stuff, I may still be guilty of a procedural mindset by
coding constructs such as this:

SELECT T1.Col1,
(<<correlated subquery>>) AS total_amount
FROM ...

and this:

SELECT DT1.Col1A, T1.Col1, ...
FROM (<<derived table subquery>>) AS DT1
INNER JOIN ...

Round here, the above is more often seen in two steps:

1) Create a query named Query1:
SELECT T2.ColA, ...
FROM ...

2) Use Query1 in another query:

SELECT Query1.ColA, T1.Col1, ...
FROM Query1
INNER JOIN ...

The idea here is that wiring one (sub)query and 'plugging' it into
another is *often* indicative of a procedural approach.

Jamie.

--

 

[Granny Spitz via AccessMonster.com]

Who said I have Access open...?

Hon, if you're *not* using Access, all bets are off. I haven't tested very
many of the permutations of other external programs using MDBs, so I'd be
guessing what the expected behavior would be. Sorry.

 

[Jamie Collins]

Hon, if you're *not* using Access, all bets are off.

Can you then recommend a good newsgroup for general Jet issues, please
<g>?

Jamie.

--

 

[Granny Spitz via AccessMonster.com]

I realise I still have some way to go because, although I no longer do
the cursor stuff, I may still be guilty of a procedural mindset

Don't worry. Your use of correlated subqueries, in-line subqueries, etc., in
Jet targets the use of set operations, not the procedural operations of a
cursor. You would have to move to another database engine like a
client/server database in order to use procedural cursors in the database
engine's propriety SQL language, like T-SQL in SQL Server.

 

[Granny Spitz via AccessMonster.com]

Can you then recommend a good newsgroup for general Jet issues, please
<g>?

This is it. You came to the right place. You're just talking to the wrong
person if you want to discuss external programs' use of Jet. Sorry.

 

[Dirk Goldgar]

No biggie: I wasn't using Access or ULS e.g. try this in Excel:

Sub test()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

Dim sSQL As String
sSQL = _
"CREATE PROCEDURE ProcTest AS " & _
"SELECT * FROM MSysObjects"

On Error Resume Next
.ActiveConnection.Execute sSQL
MsgBox Err.Description
On Error GoTo 0

End With
End Sub

It's something missing from the connection string. I tested your code
from Excel, and got the result you reported. I fiddled around to
simplify it, and got to the point where I'm running from within Access
against the same database I'm running in. (I have to close and reopen
the database after making code changes, so as not to have an exclusive
lock on the database.)

Working against the current database, I tried code like this:

'----- start of code -----
Dim conn

Set conn = CreateObject("ADODB.Connection")

conn.Open CurrentProject.Connection.ConnectionString

On Error Resume Next

conn.Execute _
"CREATE PROCEDURE ProcTestSys AS " & _
"SELECT * FROM MSysObjects"

Debug.Print Err.Number, Err.Description

conn.Close

'----- end of code -----

That code works fine, has no error, and creates the query.

If I change ...

conn.Open CurrentProject.Connection.ConnectionString

to ...

conn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & CurrentDb.Name & ";"

It does *not* work, and gives me the "no read permissions" error.

I suspect it has to do with being logged into the correct .mdw as a user
in the Admins group, but I haven't verified this. Note that this is a
database that has no user-level security applied, but all that really
means (in a practical sense) is that the default system.mdw is used and
the admin user has no password.