[thelist] Access, Jet, ODBC and DSNLess connections

[Nicole Parrot]

----- Original Message -----
From: "Scott Dexter" <sgd at ti3.com>

> > 2. the ODBC connection is the cause of the server becoming a sloth
>
> depends on the details of the connection string, you got it so we can look
> at?
>
objconnection.open "ccsr"

that's it. The ODBC itself was set up with all default options. No username,
no password.

> > 3. switching to a FileDSN is helping with the slow server,
> > but the site can
> > no longer be browsed by two persons. Could it be that the
> > FileDSN cannot be
> > shared ? How do I fix that?
>
> Should use a system DSN --you're right, a FileDSN can cause problems


>
> > 4. I am now trying to go with a DSNLess connection, but it
> > seems the keyword
> > "AS" is not recognised by the JET engine as a valid SQL word,
> > and that kills
> > 90% of my already existing pages.
>
> Let's see the SQL that breaks (Access2k should know 'AS' --its pretty
> standard SQL)

SQLUsers = "SELECT Users.UserID AS userID, "& _
                    "Users.UserName AS UserName, "& _
                    "Users.Password AS password "& _
                    "FROM Users "& _
                    "WHERE ((Not (Users.UserID)=1))"&_
                   "ORDER BY Users.UserName"

Removing the three "As somethingorother" fixes my problem
In this particular case, it's easy, cause they're not needed but I have
other places where they are. Also, removing the "Not (users.userID).." WHERE
clause does not fix the problem, so we can't blame the "NOT"

I'd give you the exact error message but you'd have it in French.  Oh my..
it just dawned on me.. Could it be that Access2K Jet engine is localised???
and "As" would use the French word????

> --You don't have a connection or recordset object in a Session variable do
> ya?
Nope. Never.
Just the User ID.

I open and close all DB connections on each page.

Thanks for the hand on this.
Nicole