I am one of the developers for an Access 97 database that is used in a small business. The database we are using has continued to grow, as has the number of users. We currently have about 15 users in the system at any one time. The database is critical to the day-to-day operations of the company. We have expierenced problems in the past when many users have accessed the same table at the same time. We want to go to SQL Server 7.0 as our backend. Right now, we have an MDE file that is linked to a set of tables that are stored in another Access 97 database. All of our record access is done through recordsets. How much work would it be for us to covert our backend to SQL Server? We are not using any ODBC objects right now, and I don't know much about them at all. Would we have to use an ODBC object to access the tables in SQL Server? How much work would it be to convert our database? Would we just need code to link to the tables and then keep our current record sets the same? Right now our database has about 40 to 50 thousand lines of code in procedures. Thanks for your help and suggestions.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Converting from an Access 97 backend to SQL Server 2
- Thread starter Arnold
- Start date
-
1
- #2
Arnold, I am a manager and not a programmer. I know of two development teams that each took a different approach to updating from VB using Access to VB and SQL Server.<br>
<br>
1. No changes to code. SQL Server tables are linked to Access tables.<br>
<br>
2. The was we did it involved design from the start, but maybe something like it could be practical for you. All of our database reads and updates pass through one common module we named DataAcc. Making use of ADO and Providers and Parameterized Command Objects our application code executables run equally well with Access or SQL Server 7.0.<br>
<br>
The data conversion utilities with SQL Server 7.0 are quite good. The only problems we had were:<br>
- Some of our table and field names from access violated 7.0 conventions.<br>
- We discovered some sloppy code that Access let us get by with but 7.0 did not.<br>
- I believe null values are handled differently.<br>
<br>
All in all we are very pleased with 7.0. I'll be glad to get more technical responses if you would like.<br>
<br>
John K
<br>
1. No changes to code. SQL Server tables are linked to Access tables.<br>
<br>
2. The was we did it involved design from the start, but maybe something like it could be practical for you. All of our database reads and updates pass through one common module we named DataAcc. Making use of ADO and Providers and Parameterized Command Objects our application code executables run equally well with Access or SQL Server 7.0.<br>
<br>
The data conversion utilities with SQL Server 7.0 are quite good. The only problems we had were:<br>
- Some of our table and field names from access violated 7.0 conventions.<br>
- We discovered some sloppy code that Access let us get by with but 7.0 did not.<br>
- I believe null values are handled differently.<br>
<br>
All in all we are very pleased with 7.0. I'll be glad to get more technical responses if you would like.<br>
<br>
John K
-
1
- #3
Arnold,<br>
<br>
As John K said in his first example, converting your tables to 7.0 (there's a wizard that is quite helpful for the inexperienced), then linking them back will allow most all of your current Access code to work.<br>
<br>
An alternative, going forward, would be two-fold. The first 'fold' would be to convert all your current module code to ASP and stored procedures, and make your access web-based. This is a process my folk have found to be very productive, with the least paradigm-shift. Note, howver, that converting to a web-based interface does call for some significant realignment of what you "know is right".<br>
<br>
The second 'fold' is the same, except for emphasis on the stored procedures - a significant change in implementation from Access protocols.<br>
<br>
ASP (Active Server Pages) provides a nice, maintainable interface with minimal support (the workstation needs a browser capable of supporting your ASP 'hardbodies', such as java applets, javescript, vbscript, etc.). All ODBC connectivity (or, if you're an MS shop, DAO or ADO) is done on the server side, so there is practically 'no' client-side installation.<br>
<br>
Mind, ASP presupposes that you're using NT 4.0 with IIS active, so this may be no help at all. (There are exceptions, but those are not really appropriate here.)<br>
<br>
<br>
As John K said in his first example, converting your tables to 7.0 (there's a wizard that is quite helpful for the inexperienced), then linking them back will allow most all of your current Access code to work.<br>
<br>
An alternative, going forward, would be two-fold. The first 'fold' would be to convert all your current module code to ASP and stored procedures, and make your access web-based. This is a process my folk have found to be very productive, with the least paradigm-shift. Note, howver, that converting to a web-based interface does call for some significant realignment of what you "know is right".<br>
<br>
The second 'fold' is the same, except for emphasis on the stored procedures - a significant change in implementation from Access protocols.<br>
<br>
ASP (Active Server Pages) provides a nice, maintainable interface with minimal support (the workstation needs a browser capable of supporting your ASP 'hardbodies', such as java applets, javescript, vbscript, etc.). All ODBC connectivity (or, if you're an MS shop, DAO or ADO) is done on the server side, so there is practically 'no' client-side installation.<br>
<br>
Mind, ASP presupposes that you're using NT 4.0 with IIS active, so this may be no help at all. (There are exceptions, but those are not really appropriate here.)<br>
<br>
- Thread starter
- #4
Thanks John K and databarn for your help. We are still looking into what the whole process will involve. I did get some good information from one of our IS guys. He gave me a file entitled "Upsizing Microsoft Access-Based Applications" I think it came from a Knowledge Base web page on SQL server(or something like that). I am still not sure though if I will need to "Set Opendatabase" or if I will need to "Set Connect" with ODBC information. Does this code need to go into the module or is it unnecessary?<br>
Thanks for your help.<br>
<br>
Arnold
Thanks for your help.<br>
<br>
Arnold
Arnold,<br>
<br>
I'm probably thinking something totally different than you. And I'm not certain who'd be 'right'!?!<br>
<br>
If you set an ODBC DSN (probably a Systm DSN if your Access tables are all on one box) to connect to the SQL server tables, all you'll need to do is to use Access's 'link table' capacity and specify an ODBC connection - the DSN previously mentioned. Then, after supplying appropriate ID and password, you simply select the tables you wish to link into your Access application. As long as your code remains 'resident' in the Access development environment, the only significant changes you'll encounter will be the inabillity to directly alter table structures, indexes, etc.. Under Access 2.0, you would use File¦Import¦<SQL datbases>. Under Access 8.0 (Office 97), you would use the File¦Get External Data¦Link option. Both work, although the 2.0 version is 16-bit and slows the system abominably. BTW, this is _not_ theory or supposition - I do this, with both versions. I don't use Access code, since I cannot count upon all my users having the appropriate licensed version on their system, so my data manipulation is done in VB: however, the connectivity issues are the same in all cases (all my cases, anyway <grin!>
.<br>
<br>
Mind you, when I first switched to ODBC, I did my utmost to make it as difficult as poszible, and succeeded in making things much more difficult than was necessary. It really is as simple as painted above.<br>
<br>
Make a good day . . .<br>
. . . barn<br>
<br>
I'm probably thinking something totally different than you. And I'm not certain who'd be 'right'!?!<br>
<br>
If you set an ODBC DSN (probably a Systm DSN if your Access tables are all on one box) to connect to the SQL server tables, all you'll need to do is to use Access's 'link table' capacity and specify an ODBC connection - the DSN previously mentioned. Then, after supplying appropriate ID and password, you simply select the tables you wish to link into your Access application. As long as your code remains 'resident' in the Access development environment, the only significant changes you'll encounter will be the inabillity to directly alter table structures, indexes, etc.. Under Access 2.0, you would use File¦Import¦<SQL datbases>. Under Access 8.0 (Office 97), you would use the File¦Get External Data¦Link option. Both work, although the 2.0 version is 16-bit and slows the system abominably. BTW, this is _not_ theory or supposition - I do this, with both versions. I don't use Access code, since I cannot count upon all my users having the appropriate licensed version on their system, so my data manipulation is done in VB: however, the connectivity issues are the same in all cases (all my cases, anyway <grin!>
.<br><br>
Mind you, when I first switched to ODBC, I did my utmost to make it as difficult as poszible, and succeeded in making things much more difficult than was necessary. It really is as simple as painted above.<br>
<br>
Make a good day . . .<br>
. . . barn<br>
Arnold,<br>
<br>
A quick Post Script (Poste Scripte?). Data access is quite simple. But table/data conversion from Access to SQL Server can be a bit more complex, what with different data types and all. The upsizing wizard you mentioned takes care of 90-95% of that drudgery, though.<br>
<br>
. . . barn<br>
<br>
A quick Post Script (Poste Scripte?). Data access is quite simple. But table/data conversion from Access to SQL Server can be a bit more complex, what with different data types and all. The upsizing wizard you mentioned takes care of 90-95% of that drudgery, though.<br>
<br>
. . . barn<br>
- Thread starter
- #7
databarn,<br>
<br>
Thanks for you help. You have answered a lot of my questions. I really had no idea what was going to be involved, but I am starting to get it now. I do have another question though. In the Upsizing manual that I have it said "do not use table-specific commands such as table-type RecordSets, DoCmd.OpenTable, or the Seek method. These operations are only supported on the local Jet tables and will break if they suddenly refer to server-based tables." Does this apply to me? If I link the tables like you suggest, could I still use the "DoCmd.OpenTable" and the "Seek" method? If I still can, I would they mention this?<br>
<br>
Thanks again for your help.<br>
<br>
Arnold
<br>
Thanks for you help. You have answered a lot of my questions. I really had no idea what was going to be involved, but I am starting to get it now. I do have another question though. In the Upsizing manual that I have it said "do not use table-specific commands such as table-type RecordSets, DoCmd.OpenTable, or the Seek method. These operations are only supported on the local Jet tables and will break if they suddenly refer to server-based tables." Does this apply to me? If I link the tables like you suggest, could I still use the "DoCmd.OpenTable" and the "Seek" method? If I still can, I would they mention this?<br>
<br>
Thanks again for your help.<br>
<br>
Arnold
Arnold, since 'Seek' relies upon an index (to the best of my memory - as mentioned, don't use the IDE), it's pretty much a gone gosling. <br>
<br>
I'd think the 'OpenTable' command would work, since you would essentially be working on a local copy of the table format, with the data being remote, but that would be something to be checked.<br>
<br>
What you'll need to do is to look at your code and consider ways to restructure it into SQL statements. That sounds rather daunting, but it's not as difficult as would first appear. It probably would pay dividends, if you are not familiar with SQL, to get a beginner's book and read it in parallel with the upsizing guide. If you are already conversant with some SQL dialect, the process will be smoother.<br>
<br>
Thing to remember, so long as you use Access as a front-end to SQL Server, is that you are going to have a mix of SQL Server commands and local Access table commands, and what works on one table is not bound to work on another.<br>
<br>
Sorry I can't be more helpful.<br>
<br>
Make a good day . . .<br>
. . . barn<br>
<br>
<br>
I'd think the 'OpenTable' command would work, since you would essentially be working on a local copy of the table format, with the data being remote, but that would be something to be checked.<br>
<br>
What you'll need to do is to look at your code and consider ways to restructure it into SQL statements. That sounds rather daunting, but it's not as difficult as would first appear. It probably would pay dividends, if you are not familiar with SQL, to get a beginner's book and read it in parallel with the upsizing guide. If you are already conversant with some SQL dialect, the process will be smoother.<br>
<br>
Thing to remember, so long as you use Access as a front-end to SQL Server, is that you are going to have a mix of SQL Server commands and local Access table commands, and what works on one table is not bound to work on another.<br>
<br>
Sorry I can't be more helpful.<br>
<br>
Make a good day . . .<br>
. . . barn<br>
<br>
- Status
Similar threads
- Locked
- Question
- Locked
- Question
