July 15, 2005 at 3:07 am
Thanks again for coming back - and I am conscious that this should all be in a new thread but.....
After reading all of your comments, I have decided to take a middle road to find my way through this major change. Suggestions that I make ammendments to the mdb before upsizing made a lot of sense. From experiments, upsizing from what you have leaves all sorts of unfathomable junk - and looking at this, it would indeed better to start from scratch.
I considered that if I tidy up what I have and make as much as possible run from recordsets, I stand half a chance of having something which will work when upsized. Or, perhaps it would run more effeciently as an mdb using recordsets than the current linked tables.
I am trying to use ADO at each instance - though still I am not sure if the code below returning recordsource for a student form should be produced in a view or sp on the server to increase efficiency?
In declarations:
Private strSQL As String
Private cnnCM As New ADODB.Connection
Private rstCMStudents As New ADODB.Recordset
In the OnLoad event:
Private Sub Form_Load()
With cnnCM
.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; " & _
"Initial Catalog=CM;Data Source=MyServer"
End With
strSQL = "SELECT DISTINCT tblStudents_Current.*, tblStudents_Courses_Current.CourseYear " & _
"from tblStudents_Courses_Current " & _
"INNER JOIN tblStudents_Current " & _
"ON tblStudents_Courses_Current.Person_Code = tblStudents_Current.Person_Code " & _
"INNER JOIN tblUsers " & _
"ON tblUsers.Team = tblStudents_Courses_Current.CollegeTeam " & _
"INNER JOIN vwSysUser " & _
"ON tblUsers.Person_ID = vwsysuser.Person_ID " & _
"WHERE (tblStudents_Courses_Current.CourseYear)= '" & [Forms]![frmAcYear]![AcYear] & "'" & _
"AND tblStudents_Courses_Current.CollegeTeam ='E & Distance Learning' " & _
"ORDER BY tblStudents_Current.Surname, tblStudents_Current.Forename"
With rstCMStudents
Set .ActiveConnection = cnnCM
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
Set Me.Recordset = rstCMStudents
Me.UniqueTable = "tblstudents_current"
Me.Forename.ControlSource = "Forename"
Me.Surname.ControlSource = "Surname"
etc.
This is all very new to me and I thank you for your patience.
Paul
July 15, 2005 at 6:36 am
I think I'd still take a day or two to really consider the real upgrade. You can use wizards to ship the data on the server then you could try to see what needs to be done on the forms to make them work... there shouldn't be a lot, but most likely stuff you don't know so you'll have some digging to do.
July 15, 2005 at 6:55 am
Paul,
I don't think that this approach is an advantage. It just is a manual substitute for what Access ADPs do automatically, and also winds up adding a connection needlessly. It also prevents Access from generating pick lists for your control sources, which you then work around by assigning control sources in code.
Also, you are using dynamic SQL when you should preferably be using either a parameterized SP or UDF (or View), or a parameterized SQL statement (with ? markers for the parameters) along with the InputParameters property.
To use dynamic SQL statements with forms, just put your SQL in the form's recordsource property, using a "?" instead of control references:
WHERE tblStudents_Courses_Current.CourseYear = ?
Then use Me.InputParameters = "? datetime = Forms!frmAcYear!AcYear." IIRC, This will cause Access to create a prepared statement that reuses the execution plan for subsequent queries.
Creating your own manual recordsets for forms will not improve scalability, and will just increase your own work. Access projects can handle at least hundreds (perhaps thousands) of simulaneously connected users, as long as the Server resources are adequate, and if you are careful with returning the smallest required amount of data to your forms. Remember, no local joins or SQL processing are ever performed in ADPs.
As an aside, Access ADPs are very smart about combo boxes, and will NOT download 1 million records to fill a combo when the form loads. It will generate incremental queries to to get subsets of the combo data when needed (when you scroll or type in the combo box.) That's why Access combos are so fast, compared to VB combos, etc. The point is that Access ADPs natively know how to do incremental queries and parameterized queries, without any special input on your part. It's true that Access uses a lot of connections, but it can be quite smart about handling SQL Server communication. In the vast majority of applications, the connection load will not be a critical issue.
HTH,
Rich
July 15, 2005 at 7:29 am
OK. I think that these comments have fully clarified the issue. I will make the big effort to move directly to adp.
I really appreciate your help and apologise for what may have seemed poorly worded questions during this thread. I've really needed a push to make this decision - especially when the application I already have works so well.
I will now do the correct thing and create a new thread for any further postings
Many thanks for all your time and effort.
Paul
July 15, 2005 at 9:25 am
just to reinforce your opinion, here's my 2 cents:
I transfered an mdb project which was slow to an 2000 adp project. Thus this is a LOT of work due to the many changes on views, code, forms, etc, it works very well when finished.
I've got 50++ users on this db + people connecting by VPN and the speed is very good even thought it's a pretty small server.
Erik
July 15, 2005 at 9:30 am
Thanks, Erik
I much appreciate your concurrence with the comments of Remi and Rich. It makes me quite certain that I'm doing the right thing.
Paul
Viewing 6 posts - 31 through 35 (of 35 total)
You must be logged in to reply to this topic. Login to reply