| Author |
Topic |
|
henrikop
Constraint Violating Yak Guru
280 Posts |
 Posted - 2002-02-11 : 16:26:59
|
Here I am once more with some MS SQL (2000) and Access (97) questions. If this is not the proper forum for this kind of questions, please say so.I really dislike Access, but I accepted a assignment, so I have to work with it.Some weeks ago I asked your opinion if it was a good idea to switch from a complete Access enviroment (beside's an ERP solution) to a SQL (data) and Access97 (GUI) solution.If I changed some heavy query's to Pass-Through query's, put some job's in SQL Stored Procedures, and so on, than this change would be OK.I ordered a brand new SQL server (2 Gb RAM, fast processor and so on), and this will arrive in a couple of days.Now I have some addition questions:(1)I have a access-form based on a table (which is for example a SQL view) with six sub-forms with related data (the sub-forms are populated with children of the parent form). For example: the mainform is a customer and one of the sub-forms show all orders of this customer. If twenty people work with Access and have this form open (and put new data in it), will this put a heavy strain on the SQL-Server? How can I make this less?(2)I *dislike* Access big-time because of the tremendous network traffic it creates. But what program can I use which for developing which is:- relative easy to add functionality (compiling VB to workable EXE files is not an option)- is fast without creating a lot of network traffic- can be used for fast developing??I write ASP but to make forms and subforms is a tough job.I write Access but I doubt the speed (and the client computers are pretty slow too)I started with ASP.NET (but is this the way to go?).Are there any other options?3)If 60 person's work with Access as GUI and SQL as database at the same time, will this be a problem?4)Who's living near Amsterdam and needs a developing job like this?  Henri |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-02-11 : 16:36:26
|
| The answer to your questions (IMHO)...1. 20 concurrent connections to SQL Server? Thats like driving a Porsche in school zone. Give it a real test.2. You can eliminate the network traffic if everybody has a copy of the Access front end on each users desktop. Then the traffic would be the same as if the front end was VB or Delphi.3. 60 users still won't tax SQL Server (assuming your database model and implementation are properly designed - this goes for any number of connections).Justin |
 |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2002-02-11 : 16:41:16
|
Henri,1) If all your access to SQL Server from Access is via "pass through" queries, then SQL Server want even now it is alive.2)I don't think so...3)No (see point 1)4)I couldn't be further away....and definately not  HTHDavidMTomorrow is the same day as Today was the day before. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-02-11 : 18:29:21
|
Henrias Justin says, as long as each user has a copy of the interface then no probs. I have one system which is almost identical to the one you're about to do and it purrs.And if you count Adelaide Australia as close to Amsterdam, then sure - call me. --I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-11 : 21:45:12
|
| Unfortunately I have to disagree with Justin and David, based on past experience. I once did an Access front end with one main form and 2 sub forms, all using linked tables to SQL 6.5. WHAT A DOG! Glaciers would race past this thing! AND THAT WAS WITH TWO USERS! While Justin and David are correct in that SQL Server can handle 20 connections without blinking, the fact that you are using a form with 6 subforms means:- one connection for the main form for each user- one connection for each sub-form- EVERY connection managed through ODBC callsSo you've got 20*7 or 140 ODBC connections, with all the ODBC overhead involved. SQL 2000 supports ODBC natively, THANK GOD, so *maybe* you won't see a huge performance hit.The problem is the way that ODBC talks to SQL Server; I'm not saying it uses cursors, but it seems that it does a SQLPrepare on every row accessed, which is equivalent to a cursor. Then this SQLPrepare is run for each sub-form...I think you get the idea. Pass-through queries won't act much differently if the users are scrolling through rows (Access/ODBC is handling the sub-form relations, not SQL Server).I think you should test it before you consider rolling it out into production, and HAVE A BACKUP PLAN. This is almost certainly a problem with linked tables using ODBC; an Access project will use ADO and should alleviate the problem, but test it to be sure.Edited by - robvolk on 02/11/2002 21:52:30 |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-02-12 : 01:46:05
|
| This isn't a helpful message per se, just a question. Why is moving everything to a VB executable not an option? I'm guessing it's either a lack of development environment, knowledge of VB, or an insane client requirement.This is similar to a project I get to start on in a week or two. I get to be the person who switches over all of our Access based programs to SQL Server/VB (I haven't ruled out ASP or something else, but honestly I don't know ASP and I'm not the one who's going to be maintaining the new system.) (The fun part is that SQL Server is mainly going to serve as the go-between to another database that we can't switch from, and have no way to implement a secure way to access. (If you give someone read access, they can read everything.))----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-02-12 : 03:11:30
|
Wow! Henri that looks good to me . if you can do it Asp.net then it should be great. I would love to assist you in this project ,you can contact me via e-mail .Nopes, i live nowhere near Amsterdam , infact am in a different continent altogtherquote:
started with ASP.NET (but is this the way to go?.
--------------------------------------------------------------"Happiness is not something you experience, it's something you remember." |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-02-12 : 04:10:08
|
| Thx for the replies and suggestions. Let me give you some more information. I work as a freelancer and I am not really a programmer, but somehow I've got a knack for building userfriendly interfaces. I worked with linked tables before and I noticed that some DBA's complained about processes on the SQL server than run forever and took a lot of resources.The thing with Access is that I can make a application in a few weeks which works perfectly fine and is changeable afterwards without even some expert eyes looking at it. What happens after some time is that a lot of users will use it, and more Access apps are built overtime. Access is a drag for your network, and speed depends heavy on the client.If I program in VB my client needs a lot of VB knowledge to maintain the database, of they have to hire the knowledge which is expensive. In a environment that changes all the time it's hard to update all the clients again and again.Even ASP is better because if it's written well, a customer can copy and paste and in this way can add functionality, and doens't have to be installed on each client everytime.ASP.NET is a different story, this will be sort of harder to maintain for my customer, but I think the solution is so good that it will be worthwhile for my customer to hire somebody who can program in ASP.NET and let somebody take a study in it. ASP.NET is defenitly the future, and what I like is that's it has some properties of a mainframe.Besides if my customer wants ASP.NET I have the oppertunity to learn it, and so I will discount my fee.My problem is that my customer already has ordered the SQL Server, and I have to change all the Access app's to Access with SQL app's.And now I have 8 Access applications with hundreds of forms and subforms and a potential of 60 users, and this makes me somewhat nervous. I have to reprogram the Access apps, and bacuse of what RobVolk said I do not want to use Access again in the new projects.I hope this wasn't boring, where I live a lot of (smaller) companies work this way (with Access as a start).HenriPS: Nazim, I will email you if I do it in ASP.NET |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-12 : 07:30:47
|
| Henri-If you want to go the ASP route, it's actually pretty easy to create subforms using frames, and if you use Internet Explorer, Iframes. I've built quite a few little apps using these, and man, they are addictive! It's really sweet to see only part of the screen update instead of the entire screen.The idea behind this is that each frame is an independent page (like the subforms in Access). You could open these frames/subforms by themselves, but they would look kinda weird. The subform ASP pages would therefore need to be passed some parameters that generate the output...imagine the pubs database. You display a list of book titles in the left window, when you click on a title, a list of sales for that title appears on the right. The right-hand ASP page accepts a title parameter and value, and it runs an SP to return the sales for that title.The nice thing about this is that once the main frame loads, all of the subframes are manipulated using client-side code. Each frame is a window, and you can reload each one individually using JavaScript or VBScript. Something like this:<script language=JavaScript>window.salesframe.location="http://myserver/sales.asp?title=1234";</script>As you can see, the URL for that window can be constructed from data gathered from the other pages. The the sales.asp page is:<% rem code for open ADO connection and recordsettitle=request("title")if title<>"" Thenrs.Open "EXEC GetTitleSales @title=" & title, connObjEnd Ifrem code to create HTML table with sales results%>There was a great article on ASPToday a few days ago that had an example of this, however it's a subscription site. You can join for $10 a month or $99 a year. Let me know if you want to check out that article.http://www.asptoday.com/content/articles/20020207.asp?WROXEMPTOKEN=196924ZcBt21jx1H12DkBMqfjkEdited by - robvolk on 02/12/2002 07:31:45 |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-02-18 : 17:18:32
|
| Thx again for great help and support.I got my first ASP.NET assignment!! BUT,before I go that 'route' I need to convert the Access data to the SQL server.At home I set up a SQL Server, Client etc. I converted the Access tables to SQL Server. Had some problems, but solved them pretty much on the spot (Bit fields, Key's etc.).Now I'm converting some query's to Pass-Through query's. How can I make these *** Updateable *** ???Besides I get a lot of write errors. I use a lot of VBA to perform certains tasks. Some write operations give Record Locks -VBA is saving and writing in the same table as the opened form- . With Access this isn't a problem but it seems ODBC locks the record longer than I expected? Any suggestions?Henri |
|
|
|
nizmaylo
Constraint Violating Yak Guru
258 Posts |
Posted - 2002-02-18 : 21:02:44
|
| Forget about using VBA and since you've got SQL Server now, re-write them all into stored procedures using plain INSERT, UPDATE, DELETE.You'll be able to utilize SQL Server much better that way.helena |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-02-19 : 02:34:10
|
I know now that Pass Through *are* snapshots. Good for reports, not for forms (I already wonderdered about the performance).Helena, while trying to get to sleep I thought about making all the VBA functions as stored procedures. I *Love* Stored procedures, they are beautiful!Still I think that Record Locks will be my biggest issue.If I open just ONE (linked SQL) table and start changing it right away (clicking in one record change, go to another record and change it) I already get write errors. This kept me awake  .Does anyone know articles about Access with SQL tables and Record Locking?Henri |
|
|
|
henrikop
Constraint Violating Yak Guru
280 Posts |
Posted - 2002-02-19 : 15:49:21
|
| Ahh! I found one problem with record locks .If you import Access to SQL, Currency fields are converted to Money. Pretty Obvious, BUT somehow these currency fields make trouble. This may have to do with the the fact that US uses dots in currency ( $ 10.50) while in (Holland at least) Europe we use the comma ( € 10,50 ). If I write in the table I get really fuzzy ODBC errors. This is the case also with ntext fields (Memo fields).When I converted these Money fields to Real, my problem was solved....Can anyone contradict or acknowledge this?HenriEdited by - henrikop on 02/19/2002 15:57:28 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-19 : 18:35:21
|
| Money may not be a well-supported ODBC datatype; it's been around for a while, but real is a much more established data type. It could also be a problem with your ODBC drivers. ODBC is very finicky with Access too, there are always some problems with certain datatypes, especially text and ntext, and it probably doesn't recognize or translate money to currency properly. |
|
|
|
|
SQL Server with Access front end