Access behavior with SQL Backend

[pbaldy]

It also sounds like you usually use VBA to build query and address the backend directly as opposed to calling a stored query, which lets you get around the differences in SQL dialects, correct?

Actually no, I typically use stored queries. I agree with the theory that stored queries are more efficient than those created and executed from VBA, and typically faster than recordset loops. I only build queries in VBA when there's something dynamic about them. Search forms and reports come to mind, where users can choose the criteria they want to apply, or where there's a multiselect listbox involved.

I actually tested a form based on a big table being opened directly or with a wherecondition, and it was noticeably faster with the wherecondition, so circumstantial evidence says that only the filtered recordset is coming over the wire. I've had no performance problems, and I've got tables with well over a million records in them.

 

[Banana]

*bump*

One more question I need to check; as subforms is pretty much unique to Access (don't know of any other FE clients that has similar feature?), will this be problematic to use subform with a SQL backend?

 

[pbaldy]

No; I use them all the time.

 

[RoyVidar]

With reference to another thread - there must be a way to identify each record, which means that each table must have a primary key or unique index (but then, one might argue whether a table without a primary key or unique index is indeed a table?)

 

[Banana]

pbaldy- thanks for the reassurance. Much obligated!

Roy- I simply can't think of a good reason for a keyless table, if there's ever such a thing. I can think of tables that doesn't need autonumber/surrogate keys (e.g. state abberivation table), but no key at all? Nah.

 

[Banana]

Yet another question...

Would it make more sense to enable ANSI-92 SQL option in Option pane for the database so the SQL being used will at least conform to ANSI-92 (which I undertand is fairly universal to all SQL dialects)?

 

[RoyVidar]

I don't think I would ever consider switching ANSI mode in an existing app. See for instance the topic "Why should you avoid mixing queries created under different ANSI SQL modes" here http://office.microsoft.com/en-us/access/HP030704831033.aspx. Perhaps for a new app?

 

[Banana]

Roy, the database I'm building is still in development and I only have a handful of queries, so not too late to switch to ANSI 92.

Thanks for the link, too. It was a great introduction and did a good job of explaining the pitfalls of mixing dialects, but the articles doesn't really detail the benefits of using one over other... Maybe I'll google a bit more.

 

[RoyVidar]

Warning - opinions
While I am fond of ANSI-92 SQL syntax, I haven't bothered changing mode in any mdb yet. My reasoning is that since the Access interface is very much DAOish, and this syntax is closer to Jet syntax than the Access dialect, I doubt you can get full benefit of it. Also, since MS recommends ODBC linked tables over ADPs (except for some cases), I think it makes more sense using "native" methods.

Anyway, one of the reasons for using this mode, at least in my eyes, would be to utilize some of the new methods of Jet 4.0 (only seven years "new" ) that isn't supported through the interface, but sadly, switching mode doesn't make any difference, they'd need to be maintained either through code or DDL.

See for instance Allen Brownes article on "ON DELETE SET NULL" http://allenbrowne.com/ser-64.html (the sample db, contains both ADOX and DDL samples)

Or CHECK Constraints - did you know that Jet supports not only field and column level constraints (as you can find in the Access interface), but also table level constraints (this can't be done through the Access interface)?

Here's a short sample showing a constraint based on a subquery on another table

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

cn.Execute "CREATE TABLE tblCustomers ( " & _
   "CustomerID INT CONSTRAINT PK_tblCustomers " & _
      "PRIMARY KEY,  " & _
   "LName Varchar(50) NOT NULL, " & _
   "FName Varchar(50) NOT NULL, " & _
   "Phone Char(10), " & _
   "Email Varchar(50))"
cn.Execute "CREATE TABLE tblCreditLimit (Limit DOUBLE)"
cn.Execute "INSERT INTO tblCreditLimit VALUES (100)"
cn.Execute "ALTER TABLE tblCustomers " & _
   "ADD COLUMN CustomerLimit DOUBLE"
cn.Execute "ALTER TABLE tblCustomers " & _
   "ADD CONSTRAINT LimitRule " & _
   "CHECK (CustomerLimit <= " & _
      "(SELECT Limit FROM tblCreditLimit))"
cn.Execute "INSERT INTO tblCustomers " & _
   "(CustomerID, LName, FName, CustomerLimit) " & _
   "Values (42, 'Dent', 'Arthur', 200)"

I don't know about the current release of SQL server, but the previous version could not do anything like this, and I don't think the current version supports it either.