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

Granny Spitz via AccessMonster.com wrote:
Regardless of which data access library is your preference, if
you work with Access, you'll never gain the respect of others who think they
work with *real* databases.

Of course, if you get into the habit of using ADO the eventual move to
a *real* SQL DMBS will be a happier one g.

Here's my advice: if someone specifies an explicit constraint (e.g.
must be ADO) then consider it a challenge on you to come up with a
proposed solution that works within the given constraint, rather than
try to convert them to another religion

Here's the reality: when answering questions, most people are under time
constraints. People will paste in existing code if their example might help
the questioner.
If they have to write code, then the question is more likely
to be skipped.

To the detriment of the poster, IMO. If they took the time to take up
the challenge they may learn something themselves. A win:win situation,
you could say.

The more specific the requirements the questioner asks for,
the fewer responses he'll get.

I'm more of a 'quality, not quantity' person myself.

Not to mention the avoidance
of being crossed off of people's Christmas List because whatever they have to
offer would be deemed unacceptable, but I was trying to be subtle.

My yuletide motivations differ from yours, it seems. I've found that a
homemade present, bespoke for the recipient, to be the most cherished.
On the other hand, santa in the department store has the gifts ready
wrapped in advance...

Jamie.

--

Granny Spitz via AccessMonster.com wrote:
Your code is creating two
connections to a database object in the default workspace, meaning you don't
have exclusive access to the database object containing that system table in
order to lock it.

Thanks but I don't think that is a plausible explanation e.g. can you
execute a CREATE PROCEDURE in a workspace?

Anyhow, no worries: I simply fudged the connection string

Jamie.

--

"Jamie Collins" wrote in message
ups.com
Granny Spitz via AccessMonster.com wrote:
Your code is creating two
connections to a database object in the default workspace, meaning
you don't have exclusive access to the database object containing
that system table in order to lock it.

Thanks but I don't think that is a plausible explanation e.g. can you
execute a CREATE PROCEDURE in a workspace?

Anyhow, no worries: I simply fudged the connection string

Jamie, I'm a little curious as to what exactly you're doing that is
causing the error. I just tested the following code, and it worked
fine:

Dim sSQL As String

sSQL = _
"CREATE PROCEDURE ProcTest AS " & _
"SELECT * FROM MSysObjects " & _
"WITH OWNERACCESS OPTION;"

CurrentProject.Connection.Execute sSQL

I had thought, based on the preceding discussion, that I might need to
use CurrentProject.AccessConnection, but that seems not to be the case.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Granny Spitz via AccessMonster.com wrote:
Your code is creating two
connections to a database object in the default workspace

Thanks but I don't think that is a plausible explanation

Hon, if you have Access open, the default workspace is open

Who said I have Access open...?

Jamie.

--

Dirk Goldgar wrote:
Anyhow, no worries: I simply fudged the connection string

Jamie, I'm a little curious as to what exactly you're doing that is
causing the error. I just tested the following code, and it worked
fine:

Dim sSQL As String

sSQL = _
"CREATE PROCEDURE ProcTest AS " & _
"SELECT * FROM MSysObjects " & _
"WITH OWNERACCESS OPTION;"

CurrentProject.Connection.Execute sSQL

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

Thanks,
Jamie.

--

Granny Spitz via AccessMonster.com wrote:
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.

--

Granny Spitz via AccessMonster.com wrote:
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 wrote:
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.

--

"Jamie Collins" wrote in message
oups.com
Dirk Goldgar wrote:
Anyhow, no worries: I simply fudged the connection string

Jamie, I'm a little curious as to what exactly you're doing that is
causing the error. I just tested the following code, and it worked
fine:

Dim sSQL As String

sSQL = _
"CREATE PROCEDURE ProcTest AS " & _
"SELECT * FROM MSysObjects " & _
"WITH OWNERACCESS OPTION;"

CurrentProject.Connection.Execute sSQL

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)