I'm considering having a single front end for both Jet backends and SQL Server backends.

I would like to know what experienced developers think of this idea. Here is some additional information.

1) The current front end uses DAO extensively and virtually no ADO. While I know that ADO is preferred for SQL Server, the current front end actually accesses data faster from a SQL Server backend on a server than a Jet database on a server. So relative to a Jet backend located on a server, SQL Server seems to be faster even though all SQL calls are from the front end using DAO.

2) The only code change that I think I still need to add is the initial linking. Currently the front end accesses the Jet backend via RWOP queries because all backend permissions have been removed from the Jet tables. This will need to be changed for SQL Server since we as the developers no longer have control over the backend and its security.

The front end currently has a versioning table to which I have added a backend type field. The backend type is either 'Jet' or 'SS' (SQL Server). So when the front end loads it can look at this table (which is set by us as the developers and is not available to users) to decide which startup linking code to run. This backend type will also be displayed on the switchboard along with the front end version number.

The big advantage for us is only having to maintain one front end and its code.

Does this sound like a reasonable plan or am I missing something here? Let me just state that at this point I have no intention of changing the DAO to ADO as this would be a mammoth undertaking, particularly in light of preliminary tests that show the access to data on SQL Server on a server is faster than with Jet on a server.

Thanks for advice, suggestions, comments.

It is quite reasonable...

Using DAO in an MDB front end just utilizes the linked tabledef, which is a DAO object as well as an ADO object through ADOX.

This situation you describe is a configuration I have used often. Actually, I use it a lot because then the back end can be in ANY ODBC compliant database (Oracle, SQL, Access, MySQL, etc) with out really changing your code base since you are using linked tabledefs. One thing to watch out for though, ODBC does NOT like NULL bit fields!! .. So if you have fields in your tables that are of type 'bit' (Jet equivalent of Yes/No), set them to REQUIRED and set the default value to a value that is appropriate (In SQL Server: 1 = True or 0 = False). Also note that the "base" date (date serial of zero) for SQL server is 1/1/1900 and for Jet it is 12/30/1899 so if you manipulate dates by just adding integers and such, you will probably want to convert to formulas to utilize true date arimatic...

DO NOT USE --> (MyDate) + 1
DO USE --> DateAdd("d",1,MyDate)

That SHOULD do the math correctly ... or just store your dates as TEXT and covert as needed.

HTH ...

Brent:

Thanks for the tips. I did some preliminary testing with SQL Server about two months ago (I had never used it before) and I remember that my forms that had listboxes which included reading a table Yes/No (or True/False) value in Jet, showed up as 1 or 0 when read from SQLServ. I didn't think much of it at the time figuring that I could fix it later. I will take your advice on the tables and will change them in Jet. However, I'm wondering will I not be able to use Yes/No or True/False as an alias when I want to display SQL Server bit fields in a listbox. My users will not understand what 0 or 1 (-1?) means and I don't think they should have to.

I guess textboxes could be formatted to display Yes/No, but as far as I know listbox columns cannot be formatted with the properties sheet. Does this mean I'm going to have to do something fancy in code?

Thanks.

Also, a question on whether the RWOP queries will continue to perform their intended function. With SQL Server tables I have no control over the permissions granted. Currently with Jet, if the front end queries are imported into a new Access DB, the queries will import, but when double-clicked a message comes up saying "You don't have permissions..." or maybe it's "The table structure can't be read.....". Either way, the user can't view the data in the Jet tables, because permissions to the tables have been revoked.

I assume that with SQL Server users will be able to view the data of the imported RWOP queries from my front end. So, I'm not sure what to do about this if anything.

I do have workgroup security but people who can sign on will be able to import the queries into a blank DB and, of course, we all know how easy it is to crack the workgroup file.

Thanks.

Edited by: rdemyan on Tue Nov 29 17:28:14 EST 2005.

With respect to the list box ... you should create a Query or SQL statement the evaluates the value of your boolean field, then puts text in its place...

SELECT MyTextField, IIF(MyBooleanField <> 0, "Yes","No") As IsChecked FROM tblMyTable

This way the list box will show a value of Yes or No in the list and not 0 or -1.

With respect to the RWOP queries ... I don't have an answer that I can write in the time frame I have right now .. I will TRY to get back to you soon ...

I would imagine you could use...

ABS(MyBooleanField)

...and write the code to always evaluate as 1 for true. (You wouldn't have to use an IIF) Just a suggestion.

If you wish to NOT use the IIF() function, then you may want to consider using the FORMAT() function:

Format(MyBitField, "Yes/No")

Which may yield a performance benifit over the IIF().

Any which way you do it, if the back end is NOT MSAccess/Jet, I THINK (unable to test right now) ... with a list box control, you have to "massage" the bit value into the text (or a 'format') you wish to see. Also, as a side note, the standard value for FALSE is 0 and TRUE is standardized as NOT 0, thus the reason that, in MSAccess, the value for TRUE is -1, which is actually a boolean value of 11111111 and is digitally equivalent to decimal 255...but in SQL Server, TRUE is 1. That is why the FORMAT(MyBitField, "Yes/No") will work ... if the value of MyBitField is 0 the FORMAT() function will display "No", otherwise it will display "Yes". Also, it is because of this standard, that you may encounter code that ALWAYS tests for <> 0 instead of checking for TRUE or FALSE like this:

This technique is supposed to help make code text more "portable" ... Or ... sometimes you will see things like this ..

which is completely valid because when the code is compiled to machine language, it will "read" "if x <> 0 then ..."

Do I follow this technique ... not at all ... in my code ... "IF x = TRUE Then ..." is how you would see it .. why am I writing all this ... because I typically "peg the geek-meter" with obscure information ... enjoy!

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

Correction ...

>>>which is actually a boolean value of 11111111

Should be:
which is actually a BINARY value of 11111111

Format(MyBitField, "True/False") will also work if you want True or False instead of Yes or No.

>>>... question on whether the RWOP queries will continue to perform their intended function.

I beleive the message you get when you import the RWOP queries is due to permissions on the Query object and not the tables that the query uses as its data source. RWOP stands for Run With Owners Permissions, which, I think, means to get the data from the sources with permissions equivalent to the Owner of that Source. However, the QueryDef object itself has permissions associated with it, which are in the workgroup file you use to secure your FE. So ... from all this, it SEEMS as though your RWOP queries will do the same thing.... protect your data from being accessed outside of your application. Please note that I personally do not have a lot of experience with RWOP queries, I have never used that technique in my applications, I have only supported folks who have used them. I have added a response to you on another thread with respect to being able to import an object (tabledef or querydef) and interrogate its properties here.

Guys:

I implemented the changes you recommended, but I realize now that the boolean/checkbox one is going to give me problems.

Why, because in some of the code I assigned the value of the listbox to a boolean variable. As a result of the format statement, the value in the listbox is no longer a boolean. But the variable needs to be a boolean for other processing.

I don't want to rewrite a bunch of code. Is there a way to "deformat" the formatted listbox value back to it's original state so I can assign it to the boolean variable.

Thanks.

Okay, I guess I can use something like:

BooleanVar = IIf(Nz(Me.Listbox.Column(2),"") = "No", False, True)

This should work for both a SQLServer backend and Jet backend, right?

Thanks.

Couple of thoughts ...

I feel is the most appropriate solution is to add another column (make it the first column if it is the bound column) to your list boxes that is hidden (width of 0) and have it be the value of the bit/boolean field. The new hidden column should also be set as your bound column. Using this method your code does not change .. just your Row Sources in your list boxes... BUT the change is compatible with SQL Server and MSAccess/JET ... so you would then have it like this (assuming the bit value is the bound column)...

Control: List Box
Row Source: SELECT MyBitField, Format(MyBitField,"Yes/No") As MyBitFieldFormat FROM MyTable
Column Count: 2
Column Widths: 0",1"
Bound Column: 1

Then, as indicated, your code would probably not change at all ...




Another possibility ... from a code perspective is just use the FORMAT() function again ...



EDITS ADDED: If you do not wish to have the bit field as the bound column you can put it anywhere and just get the value of it with the .column() property.... just as you indicate you do in your previous post.



Edited by: datAdrenaline on Thu Dec 1 0:41:21 EST 2005.

Hi Brent:

Thanks for the response, but I didn't think anyone was going to respond so I already implemented the IIF statements I mentioned in my previous post.

Seems to be working fine in Jet. I won't know about SQL Server for at least a couple of days. So let me know if you anticipate any problems from this.

Thanks.

The method you implemented should be fine with SQL Server ...

But I do have a suggestion ... I bet that's a surprize! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/frown.gif) .... In your code:

BooleanVar = IIf(Nz(Me.Listbox.Column(2),"") = "No", False, True)

It should be noted that .Column() property will return a NULL value ONLY if there is NOT a row selected, if a row is selected and then back-end source field is a Null, the value returned is a ZLS (""). Also, in your current line, IF a Null value is returned from the lisbox, a TRUE will be assigned to your boolean variable ... in general it seems that a Null should be equivalent to a FALSE ... if that is the case please consider the following change to your code snippet:

BooleanVar = (Nz(Me.ListBox.Column(2),"No") = "Yes")

With this method, the value of the listbox column is compared to the text value of "Yes", the result of the comparison is then written to the boolean variable. Thus the ability to eliminate the IIF() function. Nulls are converted to "No" just for clarity.




Also ... just in case ... I have a CORRECTION to make for my previous thread ...

This code snippet ..
x = Nz(Format(Me.YourListBox,"True/False"),False)

Does NOT seem to work (at least in A2003) IF the list box returns a "Yes" or "No" text value ... when I wrote the post I was using A97 ... I thought I tested it, but apperently I did not do the test well!

HTH ...

Thanks, Brent,

Good point about the Null. I decided to just use the same code I have and replace "" with "No" in the Nz function. I'm under pressure to deliver and I just don't want to risk to many code changes.

Thanks.

Glad to help ...

>>> I'm under pressure to deliver ...

I COMPLETELY know that feeling!! .... GOOD LUCK!