If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access and SQL
I have a Access database split front end Mde and backend
mdb in a network environment, with about 30 workstations that can run it using Access runtime. Most of these are data entry at a low level of input. The front end is presently 62.3 MB and the back end is 39.6 MB. Recently, I had a conversation that left me with some concerns. I have not had problems to this point but the individual I was talking to said that I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? 2. What size is critical? 3. What steps should I take to protect myself from trouble? SQL Recently we have installed a SQL Server for another database we use. I originally chose Access knowing that it could be moved to SQL. I am in the process of learning SQL. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. Questions: 1. Is it difficult to change the database to SQL? 2. Should I keep Access as the front end with the SQL backend? 3. Will the change require a lot of programming? (Someone suggested that front end design should be done in Visual Basic.)? 4. How can I prepare for this move? I would appreciate very much any insight into what is involved with this process. If I should post these questions on a different site please let me know. Thank you in advance, Allan |
#2
|
|||
|
|||
Access and SQL
See comments in-line.
-- HTH Van T. Dinh MVP (Access) "AHopper" wrote in message ... I have a Access database split front end Mde and backend mdb in a network environment, with about 30 workstations that can run it using Access runtime. Most of these are data entry at a low level of input. The front end is presently 62.3 MB and the back end is 39.6 MB. Recently, I had a conversation that left me with some concerns. I have not had problems to this point but the individual I was talking to said that I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? Not true for a well-designed / implemented database. It depends on how well the database is designed and implemented. For users, Larry Linson regularly reports that he has JET databases that run well with 70 users. Personally, I look to move to SQL Server at about 25-30 regular users (using the database 4 hours during a normal working day). However, I move to SQL Server because of the back-up facilities on SQL Server (full & incremental back-up as well as transaction log / transaction log back-up) rather than difficulties with JET Back-End. OTOH, 2 or 3 users may be too much for a badly-designed database! See next for size. 2. What size is critical? same as 1 The biggest JET Back-End I have had is about 130 MB (compacted) and I didn't have any problem. However, my intention is to upsize to SQL Server at about 250-300 MB, simply because I think if it is that big storing valid data, it is important enough to have proper database / transaction log back-up procedure. 3. What steps should I take to protect myself from trouble? Regularly compact and back up the Back-End. Back up daily if you can. (IMHO, back-up / BU procedure is a weak point for Access / JET) SQL Recently we have installed a SQL Server for another database we use. I originally chose Access knowing that it could be moved to SQL. I am in the process of learning SQL. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. Questions: 1. Is it difficult to change the database to SQL? Personally, I didn't find it is difficult to upsize from JET to SQL Server. Tony Toews has some info on his Web site: http://www.granite.ab.ca I think there are some white papaers on Microsoft Web site also. 2. Should I keep Access as the front end with the SQL backend? That's what I did. Use Access database (MDB / MDE) Front-End with ODBC-linked Tables from SQL Server, NOT ADP. 3. Will the change require a lot of programming? (Someone suggested that front end design should be done in Visual Basic.)? Some mods required as SQL Server Identity Field behave differently from JET AutoNumber Field. Also be careful of SQL Server BIT Field vs JET Boolean Field. If you use Recordsets, you will need to modify some arguments when opening a Recordset (dbSeeChanges option, etc ...). If you are presently use DAO, you may want to rewrite code to use ADO when time permitted (DAO works fine but some experts recommend ADO for efficiency). Visual Basic: only if you have 2 or 3 months to waste! 4. How can I prepare for this move? Grab Mary Chipman's "Microsoft Access Developer's Guide to SQL Server" http://www.amazon.com/exec/obidos/ASIN/0672319446 and read it as much as you can. I would appreciate very much any insight into what is involved with this process. If I should post these questions on a different site please let me know. Thank you in advance, Allan |
#3
|
|||
|
|||
Access and SQL
Allan
The following in-line responses are JOPO (just one person's opinions) and observations... The front end is presently 62.3 MB and the back end is 39.6 MB. This seems rather large for a front-end. Have you recently checked the original .mdb from which the .mde was created? Was it compiled and repaired before being made into an .mde? Recently, I had a conversation that left me with some concerns. I have not had problems to this point but the individual I was talking to said that I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? Scan the tablesdbdesign newsgroup for more info, but I suspect you'll find that truth is relative. And didn't you say that you have not had problems? Are you planning to add signifiantly more users? 2. What size is critical? What use is being made? Are all 30 of your current users doing data entry/transaction processing, or are they doing lookup? If you're expecting growth, what kind? 3. What steps should I take to protect myself from trouble? Is your network rock solid? At what speed does it run? Do you have backups of EVERYTHING!? Do you periodically compact and repair the back-end data (when no one is logged on and AFTER you've taken a backup)? SQL Recently we have installed a SQL Server for another database we use. I originally chose Access knowing that it could be moved to SQL. I am in the process of learning SQL. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. If your current approach is working without issue and you anticipate no growth, why change to SQL Server? 1). More robust back-end. 2). Faster response. 3). Keep your SQL DBAs employed. Questions: 1. Is it difficult to change the database to SQL? Migrating your back-end data to SQL Server can be relatively painless. Ensuring that it's gotten there, in the correct form (data types), and with proper permissions, is less painless. 2. Should I keep Access as the front end with the SQL backend? Access makes a fine front-end for a variety of back-end sources, including SQL Server. 3. Will the change require a lot of programming? (Someone suggested that front end design should be done in Visual Basic.)? Undoubtedly! Some of the queries you now use for forms and reports will "dim the lights" once you move the back-end data to SQL Server. This is because you have used Access-specific functions in your queries (I'm guessing here) that SQL Server won't understand. This will require Access to download all the table info from SQL Server -- your network gurus will HATE you! The work you'll need to do is modify your queries to use SQL Server-only functionality (check into SQL Server "views", and "stored procedures") as a first step. This will return the minimum data set. THEN you can build a second query on the first, and add in your Access-specific functions. 4. How can I prepare for this move? If you decide you have to move, plan on considerable re-development and testing time. One of the great books on topic is by Chipman and Baron: "Microsoft Access Developer's Guide to SQL Server." -- Good luck Jeff Boyce Access MVP |
#4
|
|||
|
|||
Access and SQL
Van,
Thank you for your valuable information. How does the change to SQL impact forms and reports? Will code in the events of the form remain the same. (For example an After Update event that contains DLookup, DSum, DCount etc. that look at the backend and give information to the end user through a MsgBox with options on how to proceed.) Thanks again, Allan -----Original Message----- See comments in-line. -- HTH Van T. Dinh MVP (Access) "AHopper" wrote in message ... I have a Access database split front end Mde and backend mdb in a network environment, with about 30 workstations that can run it using Access runtime. Most of these are data entry at a low level of input. The front end is presently 62.3 MB and the back end is 39.6 MB. Recently, I had a conversation that left me with some concerns. I have not had problems to this point but the individual I was talking to said that I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? Not true for a well-designed / implemented database. It depends on how well the database is designed and implemented. For users, Larry Linson regularly reports that he has JET databases that run well with 70 users. Personally, I look to move to SQL Server at about 25-30 regular users (using the database 4 hours during a normal working day). However, I move to SQL Server because of the back-up facilities on SQL Server (full & incremental back-up as well as transaction log / transaction log back-up) rather than difficulties with JET Back-End. OTOH, 2 or 3 users may be too much for a badly-designed database! See next for size. 2. What size is critical? same as 1 The biggest JET Back-End I have had is about 130 MB (compacted) and I didn't have any problem. However, my intention is to upsize to SQL Server at about 250-300 MB, simply because I think if it is that big storing valid data, it is important enough to have proper database / transaction log back-up procedure. 3. What steps should I take to protect myself from trouble? Regularly compact and back up the Back-End. Back up daily if you can. (IMHO, back-up / BU procedure is a weak point for Access / JET) SQL Recently we have installed a SQL Server for another database we use. I originally chose Access knowing that it could be moved to SQL. I am in the process of learning SQL. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. Questions: 1. Is it difficult to change the database to SQL? Personally, I didn't find it is difficult to upsize from JET to SQL Server. Tony Toews has some info on his Web site: http://www.granite.ab.ca I think there are some white papaers on Microsoft Web site also. 2. Should I keep Access as the front end with the SQL backend? That's what I did. Use Access database (MDB / MDE) Front- End with ODBC-linked Tables from SQL Server, NOT ADP. 3. Will the change require a lot of programming? (Someone suggested that front end design should be done in Visual Basic.)? Some mods required as SQL Server Identity Field behave differently from JET AutoNumber Field. Also be careful of SQL Server BIT Field vs JET Boolean Field. If you use Recordsets, you will need to modify some arguments when opening a Recordset (dbSeeChanges option, etc ...). If you are presently use DAO, you may want to rewrite code to use ADO when time permitted (DAO works fine but some experts recommend ADO for efficiency). Visual Basic: only if you have 2 or 3 months to waste! 4. How can I prepare for this move? Grab Mary Chipman's "Microsoft Access Developer's Guide to SQL Server" http://www.amazon.com/exec/obidos/ASIN/0672319446 and read it as much as you can. I would appreciate very much any insight into what is involved with this process. If I should post these questions on a different site please let me know. Thank you in advance, Allan . |
#5
|
|||
|
|||
Access and SQL
Van,
Thank you for your valuable information. How does the change to SQL impact forms and reports? Will code in the events of the form remain the same. (For example an After Update event that contains DLookup, DSum, DCount etc. that look at the backend and give information to the end user through a MsgBox with options on how to proceed.) Thanks again, Allan -----Original Message----- See comments in-line. -- HTH Van T. Dinh MVP (Access) "AHopper" wrote in message ... I have a Access database split front end Mde and backend mdb in a network environment, with about 30 workstations that can run it using Access runtime. Most of these are data entry at a low level of input. The front end is presently 62.3 MB and the back end is 39.6 MB. Recently, I had a conversation that left me with some concerns. I have not had problems to this point but the individual I was talking to said that I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? Not true for a well-designed / implemented database. It depends on how well the database is designed and implemented. For users, Larry Linson regularly reports that he has JET databases that run well with 70 users. Personally, I look to move to SQL Server at about 25-30 regular users (using the database 4 hours during a normal working day). However, I move to SQL Server because of the back-up facilities on SQL Server (full & incremental back-up as well as transaction log / transaction log back-up) rather than difficulties with JET Back-End. OTOH, 2 or 3 users may be too much for a badly-designed database! See next for size. 2. What size is critical? same as 1 The biggest JET Back-End I have had is about 130 MB (compacted) and I didn't have any problem. However, my intention is to upsize to SQL Server at about 250-300 MB, simply because I think if it is that big storing valid data, it is important enough to have proper database / transaction log back-up procedure. 3. What steps should I take to protect myself from trouble? Regularly compact and back up the Back-End. Back up daily if you can. (IMHO, back-up / BU procedure is a weak point for Access / JET) SQL Recently we have installed a SQL Server for another database we use. I originally chose Access knowing that it could be moved to SQL. I am in the process of learning SQL. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. Questions: 1. Is it difficult to change the database to SQL? Personally, I didn't find it is difficult to upsize from JET to SQL Server. Tony Toews has some info on his Web site: http://www.granite.ab.ca I think there are some white papaers on Microsoft Web site also. 2. Should I keep Access as the front end with the SQL backend? That's what I did. Use Access database (MDB / MDE) Front- End with ODBC-linked Tables from SQL Server, NOT ADP. 3. Will the change require a lot of programming? (Someone suggested that front end design should be done in Visual Basic.)? Some mods required as SQL Server Identity Field behave differently from JET AutoNumber Field. Also be careful of SQL Server BIT Field vs JET Boolean Field. If you use Recordsets, you will need to modify some arguments when opening a Recordset (dbSeeChanges option, etc ...). If you are presently use DAO, you may want to rewrite code to use ADO when time permitted (DAO works fine but some experts recommend ADO for efficiency). Visual Basic: only if you have 2 or 3 months to waste! 4. How can I prepare for this move? Grab Mary Chipman's "Microsoft Access Developer's Guide to SQL Server" http://www.amazon.com/exec/obidos/ASIN/0672319446 and read it as much as you can. I would appreciate very much any insight into what is involved with this process. If I should post these questions on a different site please let me know. Thank you in advance, Allan . |
#6
|
|||
|
|||
Access and SQL
Van,
Thank you for your valuable information. How does the change to SQL impact forms and reports? Will code in the events of the form remain the same. (For example an After Update event that contains DLookup, DSum, DCount etc. that look at the backend and give information to the end user through a MsgBox with options on how to proceed.) Thanks again, Allan -----Original Message----- See comments in-line. -- HTH Van T. Dinh MVP (Access) "AHopper" wrote in message ... I have a Access database split front end Mde and backend mdb in a network environment, with about 30 workstations that can run it using Access runtime. Most of these are data entry at a low level of input. The front end is presently 62.3 MB and the back end is 39.6 MB. Recently, I had a conversation that left me with some concerns. I have not had problems to this point but the individual I was talking to said that I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? Not true for a well-designed / implemented database. It depends on how well the database is designed and implemented. For users, Larry Linson regularly reports that he has JET databases that run well with 70 users. Personally, I look to move to SQL Server at about 25-30 regular users (using the database 4 hours during a normal working day). However, I move to SQL Server because of the back-up facilities on SQL Server (full & incremental back-up as well as transaction log / transaction log back-up) rather than difficulties with JET Back-End. OTOH, 2 or 3 users may be too much for a badly-designed database! See next for size. 2. What size is critical? same as 1 The biggest JET Back-End I have had is about 130 MB (compacted) and I didn't have any problem. However, my intention is to upsize to SQL Server at about 250-300 MB, simply because I think if it is that big storing valid data, it is important enough to have proper database / transaction log back-up procedure. 3. What steps should I take to protect myself from trouble? Regularly compact and back up the Back-End. Back up daily if you can. (IMHO, back-up / BU procedure is a weak point for Access / JET) SQL Recently we have installed a SQL Server for another database we use. I originally chose Access knowing that it could be moved to SQL. I am in the process of learning SQL. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. Questions: 1. Is it difficult to change the database to SQL? Personally, I didn't find it is difficult to upsize from JET to SQL Server. Tony Toews has some info on his Web site: http://www.granite.ab.ca I think there are some white papaers on Microsoft Web site also. 2. Should I keep Access as the front end with the SQL backend? That's what I did. Use Access database (MDB / MDE) Front- End with ODBC-linked Tables from SQL Server, NOT ADP. 3. Will the change require a lot of programming? (Someone suggested that front end design should be done in Visual Basic.)? Some mods required as SQL Server Identity Field behave differently from JET AutoNumber Field. Also be careful of SQL Server BIT Field vs JET Boolean Field. If you use Recordsets, you will need to modify some arguments when opening a Recordset (dbSeeChanges option, etc ...). If you are presently use DAO, you may want to rewrite code to use ADO when time permitted (DAO works fine but some experts recommend ADO for efficiency). Visual Basic: only if you have 2 or 3 months to waste! 4. How can I prepare for this move? Grab Mary Chipman's "Microsoft Access Developer's Guide to SQL Server" http://www.amazon.com/exec/obidos/ASIN/0672319446 and read it as much as you can. I would appreciate very much any insight into what is involved with this process. If I should post these questions on a different site please let me know. Thank you in advance, Allan . |
#7
|
|||
|
|||
Access and SQL
Van,
Thank you for your valuable information. How does the change to SQL impact forms and reports? Will code in the events of the form remain the same. (For example an After Update event that contains DLookup, DSum, DCount etc. that look at the backend and give information to the end user through a MsgBox with options on how to proceed.) Thanks again, Allan -----Original Message----- See comments in-line. -- HTH Van T. Dinh MVP (Access) "AHopper" wrote in message ... I have a Access database split front end Mde and backend mdb in a network environment, with about 30 workstations that can run it using Access runtime. Most of these are data entry at a low level of input. The front end is presently 62.3 MB and the back end is 39.6 MB. Recently, I had a conversation that left me with some concerns. I have not had problems to this point but the individual I was talking to said that I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? Not true for a well-designed / implemented database. It depends on how well the database is designed and implemented. For users, Larry Linson regularly reports that he has JET databases that run well with 70 users. Personally, I look to move to SQL Server at about 25-30 regular users (using the database 4 hours during a normal working day). However, I move to SQL Server because of the back-up facilities on SQL Server (full & incremental back-up as well as transaction log / transaction log back-up) rather than difficulties with JET Back-End. OTOH, 2 or 3 users may be too much for a badly-designed database! See next for size. 2. What size is critical? same as 1 The biggest JET Back-End I have had is about 130 MB (compacted) and I didn't have any problem. However, my intention is to upsize to SQL Server at about 250-300 MB, simply because I think if it is that big storing valid data, it is important enough to have proper database / transaction log back-up procedure. 3. What steps should I take to protect myself from trouble? Regularly compact and back up the Back-End. Back up daily if you can. (IMHO, back-up / BU procedure is a weak point for Access / JET) SQL Recently we have installed a SQL Server for another database we use. I originally chose Access knowing that it could be moved to SQL. I am in the process of learning SQL. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. Questions: 1. Is it difficult to change the database to SQL? Personally, I didn't find it is difficult to upsize from JET to SQL Server. Tony Toews has some info on his Web site: http://www.granite.ab.ca I think there are some white papaers on Microsoft Web site also. 2. Should I keep Access as the front end with the SQL backend? That's what I did. Use Access database (MDB / MDE) Front- End with ODBC-linked Tables from SQL Server, NOT ADP. 3. Will the change require a lot of programming? (Someone suggested that front end design should be done in Visual Basic.)? Some mods required as SQL Server Identity Field behave differently from JET AutoNumber Field. Also be careful of SQL Server BIT Field vs JET Boolean Field. If you use Recordsets, you will need to modify some arguments when opening a Recordset (dbSeeChanges option, etc ...). If you are presently use DAO, you may want to rewrite code to use ADO when time permitted (DAO works fine but some experts recommend ADO for efficiency). Visual Basic: only if you have 2 or 3 months to waste! 4. How can I prepare for this move? Grab Mary Chipman's "Microsoft Access Developer's Guide to SQL Server" http://www.amazon.com/exec/obidos/ASIN/0672319446 and read it as much as you can. I would appreciate very much any insight into what is involved with this process. If I should post these questions on a different site please let me know. Thank you in advance, Allan . |
#8
|
|||
|
|||
Access and SQL
Van,
Thank you for your valuable information. How does the change to SQL impact forms and reports? Will code in the events of the form remain the same. (For example an After Update event that contains DLookup, DSum, DCount etc. that look at the backend and give information to the end user through a MsgBox with options on how to proceed.) Thanks again, Allan -----Original Message----- See comments in-line. -- HTH Van T. Dinh MVP (Access) "AHopper" wrote in message ... I have a Access database split front end Mde and backend mdb in a network environment, with about 30 workstations that can run it using Access runtime. Most of these are data entry at a low level of input. The front end is presently 62.3 MB and the back end is 39.6 MB. Recently, I had a conversation that left me with some concerns. I have not had problems to this point but the individual I was talking to said that I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? Not true for a well-designed / implemented database. It depends on how well the database is designed and implemented. For users, Larry Linson regularly reports that he has JET databases that run well with 70 users. Personally, I look to move to SQL Server at about 25-30 regular users (using the database 4 hours during a normal working day). However, I move to SQL Server because of the back-up facilities on SQL Server (full & incremental back-up as well as transaction log / transaction log back-up) rather than difficulties with JET Back-End. OTOH, 2 or 3 users may be too much for a badly-designed database! See next for size. 2. What size is critical? same as 1 The biggest JET Back-End I have had is about 130 MB (compacted) and I didn't have any problem. However, my intention is to upsize to SQL Server at about 250-300 MB, simply because I think if it is that big storing valid data, it is important enough to have proper database / transaction log back-up procedure. 3. What steps should I take to protect myself from trouble? Regularly compact and back up the Back-End. Back up daily if you can. (IMHO, back-up / BU procedure is a weak point for Access / JET) SQL Recently we have installed a SQL Server for another database we use. I originally chose Access knowing that it could be moved to SQL. I am in the process of learning SQL. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. Questions: 1. Is it difficult to change the database to SQL? Personally, I didn't find it is difficult to upsize from JET to SQL Server. Tony Toews has some info on his Web site: http://www.granite.ab.ca I think there are some white papaers on Microsoft Web site also. 2. Should I keep Access as the front end with the SQL backend? That's what I did. Use Access database (MDB / MDE) Front- End with ODBC-linked Tables from SQL Server, NOT ADP. 3. Will the change require a lot of programming? (Someone suggested that front end design should be done in Visual Basic.)? Some mods required as SQL Server Identity Field behave differently from JET AutoNumber Field. Also be careful of SQL Server BIT Field vs JET Boolean Field. If you use Recordsets, you will need to modify some arguments when opening a Recordset (dbSeeChanges option, etc ...). If you are presently use DAO, you may want to rewrite code to use ADO when time permitted (DAO works fine but some experts recommend ADO for efficiency). Visual Basic: only if you have 2 or 3 months to waste! 4. How can I prepare for this move? Grab Mary Chipman's "Microsoft Access Developer's Guide to SQL Server" http://www.amazon.com/exec/obidos/ASIN/0672319446 and read it as much as you can. I would appreciate very much any insight into what is involved with this process. If I should post these questions on a different site please let me know. Thank you in advance, Allan . |
#9
|
|||
|
|||
Access and SQL
"AHopper" wrote in message
... I have a Access database split front end Mde and backend mdb in a network environment, with about 30 workstations that can run it using Access runtime. Most of these are data entry at a low level of input. The front end is presently 62.3 MB and the back end is 39.6 MB. Jeff also zeroed in on this. That sounds very large. I have a database with the following stats: total lines of code = 27369 number of Forms = 162 number of Reports = 73 number of modules = 23 number of Queries = 181 The number of tables is 55, but then since it is split then that don't matter. In access 2003, the above as a mdb is 8 megs in size. When I create a mde for the front end (and you REALLY should use a mde for the front end), the file size drops down to 6.7 megs. In fact, I can actually zip the whole thing on to ONE floppy disk with WinZip. Considering the number of forms, reports and code in the above, and the mde is only in the 6 megs range, I would serious look into your front end size. It is certainly possible that your application is 10 times the size as above, but then we would be talking about an application with 1600 forms, and 1/4 million lines of code. I would likely soon start having difficulty because of the number of users and the size of the database. Questions: 1. Is this true? When you say 30 workstations. Do you mean as a general rule you have 30 users working at the same time? ms-access will certainly handle that many users, but I would certainly be moving things to sql server with that many users. 2. What size is critical? By size, do you mean database size, or number of users? You can have a VERY small database, but with 60 users, you are pushing things. We see some people complaining in this news group that 1, or 2 users is too slow. So, the number of users is often going to be based on how well the application and the network and the workstations are setup. I mean, if access is too slow for some people with 1 user...how you going to get 30 users? I think the real important thing is ask how much work gets lost if the database is damaged? (likely, you are talking about 1 day to the previous backup). With 3 users, and loss of a 1/2 day might not be too bad. You loose the work of 30 people, and you are talking about a month of work being lost. It becomes VERY easy to justify moving the back end data to sql server when you reach 30 users. I have had someone in the Microsoft training field indicate that changing to SQL is not necessary and is or can be complicated. Necessary and complication are two separate issues. sql server is FAR FAR easier to learn then is ms-access. Sql server does not have much of a programming language (t-sql). Sql server has no forms design package. Sql server has no reports design package. In fact, about all you an do with sql server is create some tables, and create some queries. How hard can that be? Since all that sql server is a box with some tables and sql, then you are not talking about much. If your sql skills are quite good from using ms-access, then using sql server is nothing really compared to ms-access. Questions: 1. Is it difficult to change the database to SQL? You mean sql server (you already been using tons of sql in ms-access. Ms-access uses sql everywhere anyway). No, it is not hard, but you do have to learn some of the ins and outs of doing this. 2. Should I keep Access as the front end with the SQL backend? As mentioned, sql server cannot create forms, sql cannot create reports. In fact, sql server can't create any of the UI. This is why I said that sql server is easy to learn, as it don' do very much. So, you have to write the front end in something. That front end can be c++, VB, or ms-access. ms-access just like c++ is simply a development tool (it is not a database). So, if you already have a application written and running in ms-access, then it seems to me to be a good idea to continue to keep the application (why re-write it? What development tool did you have in mind?). 3. Will the change require a lot of programming? Much will depend on how good your designs are now. However, 90% or more of your code will work as is. (Someone suggested that front end design should be done in Visual Basic.)? Why? ms-access uses the same compiler and actually even using the SAME programming language as VB. I see little, or no advantage of re-writing your application to VB. 4. How can I prepare for this move? Well, start learning and playing with sql server. You do know that a free version of sql server is included on the office cd for use with ms-access? (the last 3 versions 2000, 2002, 2003 have included the desktop version sql server for ms-access). So, sql server is given away for free. You can also read some articles: http://support.microsoft.com/default...19&Product=acc ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download Center http://support.microsoft.com/?id=241743 ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download Center http://support.microsoft.com/?id=294407 ACC2000: Optimizing for Client/Server Performance (odbc) http://support.microsoft.com/?id=208858 ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download Center (a95, and a97) http://support.microsoft.com/?id=175619 HOW TO: Convert an Access Database to SQL Server (a97,a2000) http://support.microsoft.com/?id=237980 ACC: Choosing Database Tools White Paper Available in Download Cente The Choose.exe file contains a document called "Choosing the Right Database Tools" that discusses Microsoft's database products: Microsoft Access, Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open Database Connectivity (ODBC). Use this document to decide which database tool is right for you. http://support.microsoft.com/?id=128384 ACC: Tips for Optimizing Queries on Attached SQL Tables http://support.microsoft.com/?id=99321 -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada http://www.attcanada.net/~kallal.msn |
#10
|
|||
|
|||
Access and SQL
"Van T. Dinh" wrote:
Personally, I didn't find it is difficult to upsize from JET to SQL Server. Tony Toews has some info on his Web site: http://www.granite.ab.ca Random Thoughts on SQL Server Upsizing from Microsoft Access Tips http://www.granite.ab.ca/access/sqlserverupsizing.htm Tony -- Tony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Function isn't available in expressions in query expression | Reiner Harmgardt | General Discussion | 4 | July 21st, 2004 09:30 AM |
SQL Server 2000 Stored Procedures to MS Access 2000 Queries | CS | General Discussion | 4 | July 15th, 2004 03:27 AM |
TRIGGER a VIEW with SQL for a LINKED SERVER (ACCESS DB) | youssef | General Discussion | 10 | July 13th, 2004 12:00 AM |
Problem accessing SQL Server in upsizing wizard | Edward | General Discussion | 1 | July 5th, 2004 06:00 AM |
Access 2000 .adp and SQL 2000 | Wayne H. | New Users | 0 | May 3rd, 2004 08:28 PM |