VB6 to Access 2007 VBA

[syswizard]

SysWizard,
Just had a thought. You may want to ask that consultant to elaborate on the corruption. I wonder if he may have been speaking of a specific condition. I understand from others posting (having never used TS myself) that a common mistake is to provide only one front-end on the TS when each user should have their own copy of FE on the TS. Maybe that was what he referred to?

Yes, and along those lines you guys are using the ever-so-excellent STARTMDB.EXE program that is packaged as the autoFEUpdate utility, right ? I'm not sure how it works in a TS environment....does each logged-in user get their own folder/directory ?

 

[pbaldy]

I wrote my own update utility, but it does put a copy into a folder for each user, yes. I found it necessary in a TS/Citrix environment.

 

[Rabbie]

Sorry, I didn't realize a joke required that much precision, as I was aware of those points. I'll do better next time...or not.

Pbaldy 1 Banana 0

 

[syswizard]

I wrote my own update utility, but it does put a copy into a folder for each user, yes. I found it necessary in a TS/Citrix environment.

Thanks much....I am glad to see Access able to work well remotely....that's another thing I can pitch.
BTW: Does Terminal Services need a dedicated server box ?
What kind of load is put on a server with say 10 users all pounding the keyboard at once ?
Also, is T/S plug-n-play or is there a lot to config and tune ?

 

[pbaldy]

The TS system is in another city, and our guy there set it up, so I'm not sure how much trouble the setup was. Also not sure if it has to be on a dedicated box. Ours is, but the guy who set up that city LOVES dedicated servers, so it may not have to be. I doubt 10 users would stress it much; we have 40-50 on ours. I guess if you read between the lines, I didn't really have any real answers there, did I?

Rabbie, I'm ahead?!? I quit then, so I can finally win one!

 

[Banana]

Cavaet: This is just conjecture on my part!

FWIW, my old job used to have a TS, but the experience was appallingly bad. It was for only 3 users back then, but I think the problem stemmed from the fact that the server wasn't dedicated and was supporting 50 other users in different tasks (Exchange? Active Directory? Maybe more?!? ). When we moved to VPN, it was much better for our tasks. (Mind you, that was before the Access even was in the picture), so if I were going to implement TS, I would probably want a dedicated box or at least make it primary purpose.

I seem to recall see others posting emphasizing the need for a solid hardware, but not much specifics on what the recommendations would be.


Pbaldy- I thought game was over when I got eggs on my face?!? Either way you won.

 

[syswizard]

Cavaet: This is just conjecture on my part!

When we moved to VPN, it was much better for our tasks.

You bring-up another interesting point....VPN. Is it required ? Can the packets really get sniffed ? Does it slow-down the TCP/IP connection much ? Does the software layer needed cause side-effects or performance problems ?

 

[Banana]

You bring-up another interesting point....VPN. Is it required ? Can the packets really get sniffed ? Does it slow-down the TCP/IP connection much ? Does the software layer needed cause side-effects or performance problems ?

First of all, I can't say with any authority about VPN performance nor would I dare to suggest my experience with TS is representative of other TS implementation out there- this is only based on my experience (quite limited with TS- it was only few months) and from what I understood from my old IT manager.

1) We didn't use VPN and TS concurrently. It was TS, but when he (the IT manager) was hired, he chucked it out for VPN, which was a boost. Whether it can be used concurrently or not, I can't say (though I imagine it wouldn't make much sense).

2) I don't know the exact details of VPN implementation, but enough to say that it's encrypted so even though it could be sniffed, by time they figure out the encryption key, the data is long dead and useless.

3) If you are running VPN software, then it will degrade server's performance. This is why some buy VPN router or some kind of VPN hardware so server is not burdened with handling encryptions and tunnelings- from server's POV, it's just a couple more local clients.

4) I understand that VPN is much more generic compared to TS- VPN proves access to all network resources whereas TS provides access to local resources. At least, that was how it was when we used TS.

I hope others has more information for you, though. Personal experience is only useful to certain extent.

 

[Simon_MT]

1) VPN should be outside the box i.e. a separate hardware solution and whilst it is encrypted it also knows about the family of connections.

2) You can manage bandwidths and prioritisation of services so you could limit one service in favour of another.

3) Any encryption is breakable but with VPN it is harder so to mitigate intruders, you can change the shared secret, remote sites first and then local.

4) The way to look at Terminal Services it that you are trying to emulate the remote users experience with that of local users. So you provide each Terminal Server user with the almost the same functionality as local users but the RDC maps the local printer.

5) Then should be little speed degradation using VPN but good broadband connections (business strenght) are essential, traceroute will tell you how long it takes a packet to get from point A to point B.

6) There should be a separate dedicated server for the Terminal Server, not on the main file server.

Simon

 

[syswizard]

Thanks much Simon for the technical discourse....much appreciated.
I just met with my client yesterday and he indicated that he was unable to run an Access97 application using TS and RDC. However, he was able to run a VB6 application against the same AC97 MDB files on the network drive. Thus, I suspect TS is not properly configured. Also, the AC97 front-end app is not being migrated to the folder used by TS. How can I get the exact path of this folder either via UNC reference or mapped drive reference ?

 

[Simon_MT]

You can definitely run Access 97 on a TS, I've being doing it for about 5 years. Obviously, you need to load the application onto the Terminal Server.

Simon

 

[syswizard]

Oh boy, what a "duh" moment for me....that's probably it !
On a similar note, can anyone give me an idea of what the performance hit would be to run any version of Access from a file server vs. locally. Also, what about the licensing ? Does MSFT check for the number of concurrent users ?

 

[Simon_MT]

you need to purchase CAL's for each user not expensive under £100 for 5 users. The only issue with Access 97 is that it hogs memory but just load the Terminal Server with RAM about 256-512Mb per user.

Simon

 

[syswizard]

Thanks Pat for that tip on the need for Citrix to be set-up with multiple profiles.
As far as Jet goes, I know they've made improvements with the 2007 release. Nonetheless, my experience has been that for MDB read/writes Jet/DAO is so much faster than ADO. In fact I've nick-named the latter "Slo-DO". Also, I actually like having the ability to use methods like Append to build a new table and new indices, etc. I find SQL just so rigid and inflexible. I came from a 4G/L mainframe background using a product where the data access keyword phrases could be in any order. With SQL, having to worry about the group-by clause being before the order-by is just such nonsense IMHO. I am totally amazed SQL never progressed to be more flexible and friendly as a query language. Shame on the standards committee (or whomever).

 

[syswizard]

Jet uses SQL and the same rigid rules apply so I sense some confusion here. In my Access applications, I use DAO regardless of what backend database I use. There is just not enough of a difference and DAO also allows me to manuplate Access objects so it is just more flexibile for me.

No confusion....above is what I meant. The ability to have objects like queries, and then be able to clone them and modify them is just powerful IMHO. It's a shame however that PARAMETERS were never extended to permit SQL full code fragment replacements (so I created my own). I really disdain the coding of inline SQL statements. Business rules and logic are behind those queries and they're buried in the source code and not exposed to testing and modification easily.

 

[Banana]

Hopefully I'm not muddling the water here, but want to point out that SQL and VBA (as well as the underlying libraries VBA happens to use) shouldn't be compared as they have a different purpose and scope- SQL is generally a declarative language while VBA is more of an imperative language. SQL is really great when you're working with sets- something that VBA (regardless of what data access technology we may use, DAO, ADO, ADO.NET, RDO, JRO, Banana's Secret Sauce™, and actually regardless of what language we may use- it's still true even if we're using C#, C++, C, Python, VB6, VB.NET) really can't deal with handily and for most tasks would have to iterate each rows.

Also, it seems that SQL is considered a 4G/L....

The point is that SQL does great when it's used for what it's intended- manipulating sets while VBA does great for what it is made for, procedures to process something from one state to another.

 

[syswizard]

I rarely use embedded SQL either for that reason and others. Whether the back end is Jet or SQL server doesn't matter. I use querydefs about 90% of the time. The rest of the time I use embedded SQL because the criteria is controlled by the user at runtime. You can still use querydefs. Are you thinking of .adp's which are more tightly integrated with SQL Server and so more limited?

I've got no experience with ADPs. Other than security, and ability to handle millions of rows, what exactly are the other benefits of going with SQL Server or the Express edition ?

 

[syswizard]

SQL is really great when you're working with sets- something that VBA (regardless of what data access technology we may use,

Agreed.

Also, it seems that SQL is considered a 4G/L....

But a poor one at that. Good 4th or 5th G/L's don't bust your chops over rigid syntax requirements and exact statement ordering.
They're design is to solve business problems quickly, not to bog down the developer in technical details.

 

[syswizard]

Below is UNQUESTIONABLY the "Poster Child" of the special interest group known as "Programmers Who Disdain Embedded-in-source SQL and/or Touchy SQL Syntax":
Read all in it's glory here:
http://www.utteraccess.com/forums/showflat.php?Board=82&Number=1755780
-------------------------------------------------------------------------
strSQL = "UPDATE [t_table1] " & _

" SET [t_table1].dateRemoved = Date() " & _

" WHERE ([t_table1].EmployeeID = """ & Forms!YourForm!cboEmployeeID & """) " & _

" And ([t_table1].recAdded = " & Format( _

DMax("recAdded", "t_table1", "[EmployeeID] = """ & _

Forms!YourForm!cboEmployeeID & """"), "\#yyyy-mm-dd\#") & ") " & _

" And ([t_table1].dateRemoved Is Null)" In general, when construction an SQL String in VBA code, the most important thing to remember is that there are 2 completely different usages of the double-quote (or single-quote):

1. The first usage is to use the double-quotes as String delimiters in VBA to supply a literal String in the construction. The pair of double-quotes (the left-most double-quote and the right-most double-quote in the literal String component used in the concatenation) will be consumed by VBA, i.e. they won't appear in the final SQL String.

2. The second usage is to supply the double-quote to the SQL String, meaning you want the double-quote to appear in the final SQL String (and not consumed by VBA).

The 2 different usages of the double-quote plus the requirement that to include a double-quote in a double-quote-delimited String, you need to use 2 double-quotes create a myriad of double-quotes required in the (VBA) SQL String construction. This is rather daunting to construct but you will get used to it provided that you are aware of the VBA consumption. A very useful tool is the Debug.Print strSQL statement so that you can inspect the result of the String concatenation.

I am sure that you are aware of the delimiting requirements for literal values in SQL String but I list here for completeness:

1. A literal string value must be enclosed in double-quotes (or single-quotes)
2. A literal date/time value must be enclosed in hashes (#). In addition, the date value must be in the US format "mm/dd/yyyy" or an internationally unambiguous format such as "yyyy-mm-dd" which I use in the posted SQL construction.
3. A literal numeric value does not require delimiting characters.

Note also the use of parentheses I adopt. While most of the time, JET seems to be able to interpret the component criteria correctly (I had one case where using parentheses worked and using no parentheses didn't work a few years back but didn't note down the exact situation / SQL), I just feel that using a pair of parentheses around each component criterion clearly shows the logical operation, much better than without parentheses. I read in Roger Jennings' "Using Access 97" (published by Que) that the multiple parentheses created by JET / Access (when the QBE is used to construct Queries) do have their proper functions in delineating the Boolean components / operations and sometimes, multiple / nested parentheses are necessary. I basically adopt using a pair of parentheses as the delimiter for each component Boolean expression as it is easier for me to see the logic of the combined criterion, IMHO.

 

[Banana]

Syswizard,

Just in case you didn't know, you're preaching to the choir. Knowing Pat, she wouldn't use dynamic SQL too much, and has stated so in her last post.

I also had a thread discussing the merits (or lack thereof) of dynamic SQLs over there.

Since then, I've come into habit of doing what I like to call (for lack of better term or out of of my ignorance for formal terms) 'interfaces' to parameter queries I find myself referencing several times inside VBA.

Here's a bogus example:

Property Get rLastName(sID As Long, eID As Long) As DAO.Recordset

Static qdf As DAO.Querydef

If qdf Is Nothing Then
   Set qdf = CurrentDb.QueryDefs("qtrLastNames")
End If

With qdf
   .Parameters("lStart") = sID
   .Parameters("lEnd") = eID
  Set rLastName = qdf.OpenRecordset(dbOpenSnapshot)
End With

End Property

Where the qtrLastName SQL is:

PARAMETERS lStart LONG, lEnd LONG;
SELECT LastName FROM People WHERE PersonID BETWEEN [lStart] AND [lEnd];

This gives me an standard point of entry to important querydefs so if I happened to change definition of a querydef, the related property procedure will break for any calls and thus only requires me to fix it once, and best of all, I don't have to muck with formatting dynamic SQL.

Not to say they're useless- there are times where it is just easier to do it dynamically, as the thread I linked discussed, but I do feel that for most cases you can get away with a querydef, and using it to give you recordsets without too much trouble. Even so, in my experience, needing a recordset inside VBA is a minority compared to using queries as a recordsource or rowsource, and since we can just use query builder and let Access/Jet have its own way with SQL, rarely do I need to actually type out SQL statement except when I'm writing subqueries.

HTH.