Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldev80
Yak Posting Veteran
68 Posts |
 Posted - 2006-09-11 : 09:31:32
|
| I have some data in MS ACCESS and an application in MSACCESS which updates, adds and deletes the data. Since MS ACCESS does not have a multi user capability I would like to migrate the data to SQL SERVER and use the same application. Has anyone done this and would I be able to use the same application to access SQL SERVER. Any suggesstions or experiences??? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-11 : 09:33:39
|
| Well, MS Access does work up to 10 simultaneous users...However, there is a MS SQL Server Upsize guide.Peter LarssonHelsingborg, Sweden |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-09-11 : 09:51:43
|
| Experiences?Yes, I have never seen a straight conversion ever work.The "application" written in access is usually a pig. And always runs slower than the original.How much data are we talking about?Alos to make Access a muli-user application, create 2 access "databases".1. With al;l the code, and 1 with just the data.Place the data component on a server, and give each "user" the application code on their client compter. Have the code link to the data piece.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-09-11 : 10:56:56
|
| You can upsize Access, but it usually does not work very well. One big problem is that many Access applications return all the data to the client and perform client side joins.This is not the way to do things in SQL Server, so you really need to re-write the application to eliminate that. Since you need to re-write it anyway, it's better to just go to a web or client-server app and forget Access.CODO ERGO SUM |
|
|
|
sqldev80
Yak Posting Veteran
68 Posts |
Posted - 2006-09-11 : 13:38:30
|
| We are talking about a few users and very less data like 10 MB to begin withand will grow slowly. The forms are created in MS ACCESS to enter data. What other way can the data be entered by front end. Is VB is good choice.Any examples of other choices. I thought since both are MS applications even if the data is put on SQL SERVER we can use the forms to add update or delete.Would that not be possible at all. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-09-11 : 13:45:17
|
| 10 MB? Whil that's not a lot of data, depending on how your front is written, you might see a lot of locking...as I 'm sure Access is set up with "optimistic" locking.....as in you'd better be an optimist to think there won't be any corruptionHow many froms are we talking about? They right way to do this and and to keep access, is convert all forms to be unbound, and execute sql to populate a result set. Then change the rest of the SQL to DML operations.BUT! If you are going to do that, you might as well re-write, because I'm sure there are enhancements that the users want.You best bet for that many users is a web based approach, using application level security and connection pooling.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
sqldev80
Yak Posting Veteran
68 Posts |
Posted - 2006-09-11 : 13:51:53
|
| We are talking about 12-15 access forms. I don;t know what you mean by unbound. Like once we enter data in one form it should not be in a drop menu on another form.I do have some stuff like that where you enter data in a form and in anotehr form you can access if from a drop menu so that you don't have to re-type it. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2006-09-11 : 14:16:48
|
| Where's JimL when we need him?An unbound form is one that does not have a datasource property...hence unbound to any data.You need to use code to go get the data with sql statement...better yet calling a stored procedure. Then you need to use code to populate all of the controls that used to be bound.It's not a big deal...but it is time consuming, and the correct methodology for Access to "talk" to SQL Server.There is of cource Access Data ProjectsWhat's your background?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
sqldev80
Yak Posting Veteran
68 Posts |
Posted - 2006-09-11 : 14:46:03
|
| I am a MS SQL developer and have been assigned this small task for MS Access. When you say"You need to use code to go get the data with sql statement...better yet calling a stored procedure. Then you need to use code to populate all of the controls that used to be bound."Then do I write stored procedures in Access or in SQL Server.Can I even write stored procedures in Access?But then we would have to face the problem of redundancy by adding same data multiple times. Else use a stored procudeure to populate.Why would bound tables be a problem when we use access forms to enter data and sql server to store it. |
|
|
|
stephenbaer
Yak Posting Veteran
71 Posts |
Posted - 2006-09-11 : 16:20:36
|
quote:
Originally posted by sqldev80
I am a MS SQL developer and have been assigned this small task for MS Access. When you say"You need to use code to go get the data with sql statement...better yet calling a stored procedure. Then you need to use code to populate all of the controls that used to be bound."
I'm no pro, but I can answer some of thisquote:
Then do I write stored procedures in Access or in SQL Server.Can I even write stored procedures in Access?
You can't write Stored Procedures in an MDB file, no. For that, you need an Access Data Project. Even an ADP isn't great, though. I'm working on something similar, and I've found it's easier to write the functions, procedures, etc., on the SQL side, then watch them magically appear in the ADP. quote:
But then we would have to face the problem of redundancy by adding same data multiple times.
I don't know what you mean by thisAs for the 'unbound Forms', here's an example:This is a Procedure that is passed 'stFacility' (which corresponds to a foreign key from tblFacilities) by the Access ADP front end. Code in the control that opens this form sets this procedure as the Record Source of frmResidents before open occurs. Access determines via the users' login credentials which facility(ies) they have permissions for, then passes it. The users can only see rows where tblResidents.facility=@stFacility. Additional code automatically sets tblResidents.facility for a new row to the appropriate string as INSERT occurs. This is how I am able to store all of our residents' confidential info in a single table, yet limit availability of that information to those who have 'need to know' for each facility. CREATE PROCEDURE qselByFacility(@stFacility AS VARCHAR (30))ASBEGINSELECT ResidentsID, Active, Facility, Last, First, Middle, Sex, Height, Weight, Eyes, Hair, SSN, PDJ, DOB, DOP, Ethnicity, ScarsMarks, PrimLang, WhyPlaced, County, Agency, CtyContact, CCPhone, CCLocation, HealthIns, DentalIns, DInsNbr, HInsNbr, DOT, DisReasonFROM tblResidentsWHERE (Facility = @stFacility)END** To those who know A LOT more about this than I (which is just about everybody), please feel free to rip this to shreds, or better yet, tell me why it is a bad/good idea to do it this way, as this is a work in progress. |
|
|
|
Hell0_123
Starting Member
1 Post |
|
|
|
|
|
|
|
migration from ACCESS to SQL 2000