So I upsized, now what? (1 Viewer)

Hi.

I have finally upsized my backend to SQL Express.
After some fiddling it went well.
By now the endusers still use the access backend, and I am still testing the SQL backend.

This post should probable be split in multiple threads, but I'll give it a shot here.

Log form usage:
I used to have a code from Allen Browne - Log usage of forms and reports, that recorded the form usage. This doesn't work anymore. It fails on DbSeeAllChanges. I changed this, and it fails on Now() vs getdate() and so on.

Instead of fixing "access code", maybe it would have been smarter to find a solution to this from within the SQL Server? What kind of audit/log possibilities do I have there? Or does anyone know some fancy code for this job that's adapted to SQL?

Setting up users:
The greatest issue so far is the fact that the IT department just gave me a server, installed SQL Express, and the rest is up to me.
The end users are in a Citrix environment, and setting up the DNS was difficult. I ended up creating a single user on SQL Server, and use this in my frontend.
Should EVERY user have their own SQL user? Or is my solution alright with this common user with read/write privileges.

And for the grand finale; since I am still testing my application before I launch.... When the time comes, how can I replace the data in the sql with data from the access backend? Must i do the whole upsizing again, or may I use an easy function to just replace the data?

And my last question;
What have I not considered yet? What is the normal issues with an application after upsizing?

On the Search use the user Rx_ and then the key word Citrix
I am not the only one using it, but have probably been in enough discussions to get you started.
Not everyone agrees but this worked for me.
I posted code for Citrix - Set up a folder on Citrix for each UserID - use the bat file to delete the last copy - then copy and paste the latest Front-End version into each individual user's folder. This way, each user has a copy of the front-end to run that Citrix session. Past experience indicates about 250 concurrent users can be connected. If everyone is just using the same single copy of the front-end on Citrix, you will encounter some big problems when multiple users are running a session.

My preference is to use SQL Server Native Client 11.0 (search on my user ID and this key word). It is the standard for AZURE, a free download.
Use DSN_Less connections (using vba) to run a script and connect your linked tables.
There is my code on this forum site, there are also some much better code examples on this site.

My preference is to use the SQL Server Migration Wizard for Access. When I use SQL Server Native Client 11.0 and this tool, it would seem that all my conversion data types went away. Again, a free tool.

I will try to find time to re-post my Logging functions. They track users on Citrix with time stamps. In my mind, extensive error trapping with a quick transaction is necessary. Nobody needs a logging function to crash. LOL Besides, it might not log its own error.

I also use Citrix with MS Access 2010 - we are co testing Access 2013
On Access 2010, make sure both your development satation and citrix Access server have the same (latest) SP. This is very, very important.

Just an FYI: I abandoned Access reports. All of my user reports use Excel Object Model Programming. This way, they look at the user id, then save as in each user's network folder, under the folder for the name of the excel report. I have done this for almost two decades with citrix. It just saves problems.

http://www.access-programmers.co.uk/forums/showthread.php?t=247756&highlight=native

http://www.access-programmers.co.uk/forums/showthread.php?t=224121&highlight=native
http://www.access-programmers.co.uk/forums/showthread.php?t=270308&highlight=native
http://www.access-programmers.co.uk/forums/showthread.php?t=223173&highlight=native

Logging:
http://www.access-programmers.co.uk/forums/showthread.php?t=191742&highlight=citrix
Citrix Bat File:
http://www.access-programmers.co.uk/forums/showthread.php?t=255458
Other smart guys that discuss Citrix
http://www.access-programmers.co.uk/forums/showthread.php?t=255296&page=2

Thank you RX_ , once again
I will read your posts on the subject!

One question. The database now works fine (from the looks of it). I've written a lot of views and so on, and I will continue to improve the application before I go live.

BUT, lets say I am happy with everything, and "launch" the new backend. How can I do that without redoing everything one more time? Can I just delete the tables in the sql database, and upsize the "live" backend tables? Will this "delete" the views I've written? Is there another way to do this?

Lets assume that on the SQL Server side, you have Views with the same name as your 1) Access Tables 2) Access Queries

Make a copy of your Access front-end.
Rename all of the Tables and Queries that will be replaced with a SQL Server Table or View with _Bak example Local Customers table is renamed to Customer_bak

If you go with a DSN-Less connection, use code to make the ODBC link to SQL Server (a Linked table).
Attached is an image of my local table LQL_Linked
It has about 300 table names.
The DNS-Less create link function first deletes all existing DSN-Less links.
The DNS-Less create link function looks at the TableName and the Linked field. - it creates the new table.

If the Linked Table has the same name as the old Local Table (now renamed with the added _bak) it will just replace the old local Table.

I am planning to use DSN Less. Then I dont have to bother IT in setting up connections.
DSN i probably something I will ask about later I got it working, but I think something is missing.

My question that really bugs me...
I upsized, and got it working. I wrote plenty of views and so on. I made a lot of adjustments to the frontend.. so I think i am ready now to go live.....
BUT! How do I replace the test data in SQLdatabase with "live" data from access?

Is there a function to DELETE all records in SQL, but keep structure... and then upsize the "live - accdb" with "data only"?

Note: I am planning to link frontEnd directly to SQL. Based on your earlier reply, it seems that you link the "old backend" to sql, and your frontend to the "old backend". Is this something I should consider?

I would say it "depends". Depending on your level of SQL Server use.
Question: do you need to keep the exact PK ID that was probably an Auto-Number in Access? In most cases the answer is most likely a resounding YES.
This means you can't use the Linked table to delete all records (from the Access front-end) and then just import the data. The autocounter would start from that point.
Basically, it would be necessary to go into SQL, remove the Auto-counter, then migrate the data (with your Access Autocounter PK), then go back and change the SQL Server Back End PK back to an autocounter.
In SQL Server the PK Autocounter in Access is called the Identy and that is where the seed and increment is set.

For example: if the Customer_ID is the PK as assigned with the PK Identy column, will your Customers being imported be re-assigned? The same for a look-up table if the PK was used and some of the records were deleted over time - so they are not exactly in sequense.

In my case, the SQL Server migrated data became dirty as the old Access front-end had daily data entry. So, here was my approach:
On a Thursday, I made the local database Read Only so users couldn't enter new data or change exisitng data during the migration.

Before that, use the tool SQL Serve Data Migration Tool for Access. It is a free download, not part of the Access migration wizard. It is tiny bit quiky interface but once figured out, it is really nice.
This tool allows the migration of one table at a time. I had over 250 tables on one database. They were in categories. Either rename your SQL Tables or delete them. Then re-migrate about 10 tables. Use the script to create a DSN_Less link to those 10. Test it out.

In SSMS (Sql Server Management Studio) Hover over one of your Tables - right click Menu -Script Table As - Drop and Create To - New Query Editor
This will drop your table and recreate the essense of your table.
Note: delete every thing including and below
EXEC sys.sp_addextendedproperty @name=N'MS_SSMA_SOURCE',

Use Save As on the Query editor to save this to a directory so you can use it over if necessary.

The core of this will look something like this:

The cool part of this is that you can probably figure out how to add an extra field and data type in this script. Welcome to T-SQL programming.
Note the first Identity column above.

[ID_NEPA] [int] IDENTITY(1,1) NOT NULL,
What if I renamed this to ID_NEPA_Auto then created a
[ID_NEPA] [int] NOT NULL ?
I have used this method to migrate data from Access Local Tables to a DSN_Less SQL table. This lets me import my Autocounter (identity) data as an integer using the Update query in Access. Then, after the data is moved over to SQL Server, use the Table design to remove the ID_NEPA_Auto and then set my ID_NEPA up as an Identy autocunter to continue with the last PK record.

This is just an example of different ways to accomplish the same thing.

Well, this turned into a missive. Will let you digest this and get back with more questions.

Just wanted to address the Access Backend (old backend) and the SQL Backend (the new target).
The front-end will link to one of these or both!
For the old Access back-end - you could use a script to take the Access Table Name = Access Table Name & "OLD"
Now use the script to link DSN_Less to all the SQL Tables.
This leaves a stage where the Table list view is now:
CustomerNames
CustomerNamesOLD

Your Access program is coded for CustomerNames
This provides a great way to quickly quality check the results.
Conversly:
CustomerNames (keep the Access Table)
CustomerNamesSQL (the empty SQL table linked)

Remember, the DSN_Less link will let the name assignment be anything, subject to change later. Sometimes, I prefer to see the old and new just to make sure the record counts, field types, and other things came across as intended.

Again, it is an option depencing on your situation. With 250 tables, I didn't do all of them at once on this database due to massive data cleanup activities. In another database case where the data quality was good, it wasn't a concern.

Hi RX_
I just want to say thank you for all your help! I have studied up on the mitigation, and I believe I have a solution that works out now.

First, I will use DSN Less, but not use the onload event since it slows down opening the application. It will remain stable for a while, and its fairly easy to implement a fresh frontend with the citrix batch.
All tables and views hooks up fast Thank you really much for this tip. Regular DSN was more complex than It looked with multiple Tservers.

The system today is usually not in use during evenings, so I have made changes to the existing access backend daily to adjust it to the sql server. When I now upsize a copy of the backend I have no errors, and is "ready to go" in a few minutes (even though the preperation has taken many, many hours, days and weeks

I have used the vanilla access upsizer. It fits my needs.

When I go live, I need to import all views from the test sql base to the live sql base. This can be done with a script. So far I haven't succeded, but I'm close It's not a big deal to import manually, just more work and more time.

The one thing I havent figured out yet is the logfile. I have checked your post on this. You use a code fairly similar to Allen Browne's and you use a DAO recordset. This function has been really nice with access backend, but I get errors now. First error 3622, must use dbSeeChanges. I havent been able to fix this, so for now I will deactivate that function. I am not sturdy enough in the differences between dao, ado, and on top of that t-sql for all the dates. I havent found similar code so far for sql backend.

One question: With access backend I have always kept one connection open to the db at all times. This to speed up the application. Is this also good practice for sql backend?

RE: not use the onload event - that is correct - as the developer you run this script once, then put the copy for Citrix to clone to each user during the Citrix session

Script each view in the TestDB with the Drop and Create as I described earlier, make one big text file. Then search and replace to replace the TestDB with the Live DB - run script - create all the views.

I think you are referencing the Persist table (local) in the first Home form. I still ue a local table for mine. The rest are on demand and will be very fast.

dbSeeChanges Thanks for the memories. I came into work after an auto Microsoft upgrade and everything stopped working.
strSQL = "SELECT tbl_Users.User_ID, tbl_Users.Activity, tbl_Users.Permit "
strSQL = strSQL & "FROM tbl_Users "
strSQL = strSQL & "WHERE (((tbl_Users.User_ID)= '" & User_ID & "') AND ((tbl_Users.Activity)='A') AND ((tbl_Users.Permit)='A'));"
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
or Set rsCountyNumber = CurrentDb.OpenRecordset(strSQL, 2, dbSeeChanges)

Adding this should work for you. When using a DBFailOnError use the + dbSeeChanges

Once again, thank you!
I got the logfile to work with a combination of dbAppendOnly + dbSeeChanges.

Think i'll mark this as solved. We are now live and kicking !

Congradulations! and welcome to the world of Citrix.
When you start working with reports, be sure to let me share why I use Excel for reports rather than use the Access Reports.

Since you are on SQL Server Cirtix, one final comment.
There just can't seem to be enough log files! LOL
For example:
Log a form name and Primary Key plus other data every time a user deletes a record.
Using Excel for all reports, Log every time each Excel report is run (user, date-time, ...) for Usage Stats
and so on.

Well, I mixed the two together. Today I am writing a filter for the report log stats.
If I had only created a log table just for the reports, I would be finished with this mundane task by now!
Nice thing about SQL Server.... 250 linked tables is still not enough!