Access to SQL to ASP
-
We have a regional Access DB which they want to take corporate wide. I figured the the best thing to do would be to migrate the BE to SQL and keep the mdb FE and just link it to the SQL BE. At that point I should be able to develop an ASP FE for the rest of the corporation to access via our intranet. I would appreciate any thought, concerns, or personal experiences with this.
Rob
-
I think best option is DTS
My Blog:
http://dineshasanka.spaces.live.com/ -
You could also leave the access MDB as it is, and have your asp pages connected directly on the access database. In that case you don't need to migrate to SQL server.
The only difference in your ASP pages, is your connection string.
I used this once to make an access database available to a small workgroup of people, to avoid installing MS Access on their pc's.
Bert
-
The fastest solution is as you stated, SQL BE with Access .mdb FE. You will get faster performance in the corporate setting with an Access .adp FE but it requires a complete re-write of the app. Most Access developers prefer .mdb
SMK
-
If you already have to manage a SQL Server(s) and are familiar with administering SQL, I would migrate the db. It also depends on how large the Access database is or will be. It may be easier at this point then later. Just my 2 cents worth.
BK -
We just recently went through this. All we did was create the Access Tables in SQL Server with our naming standard but left field names as they had been named in Access. Then we linked the tables from Access and renamed the linked table in Access to the same name is use to be in SQL Server. With the field names being the same and the renaming of the linked tables to match the old Access names, we did not have to change the queries in Access. Hope this helps
-
We too have upgraded many MS access db's to SQL Server. It is a fair amount of work but it is worth it if you want to go that way. We converted many using multiple methods but DTS was the best option by far for us because of the sheer volume we had to convert. If you only have one or two then use the wizards in SQL to import the data.
We noticed an immediate increase in speed, better control over security, less db and table locking issues, backups were much easier to do, not to mention the ability to do stored procedures, and several other minor benefits.
One word of caution is how Access does it's record locking when it is linked to a SQL table. We have had problems with this and I don't allow them to do this anymore. I would think that once you get the ASP FE built that you could get get rid of the Access FE.
Good luck!
SJ
-
I have done this many times as well. We had a very dynamic data structure as well. We were constantly creating new linked tables to look at. If you need any code to dynamically add all of your tables from SQL Server let me know.
Richard
-
Thank you all very much for your help and support. I'm definately going through with this due to the nature of this DB and the customer base it affects. Thanks again.
Rob
-
Richard: dynamically creating SQL tables: Do you mean using ADO and creating temporary tables to populate forms on the FE? If so, I'd like to hear more about that as that's the direction I'm trying to go.
SMK
-
Actually, I create the linked tables on the fly. The way I handle the temporary tables is to use ADO to call stored procedures and then I have a routine that will transfer the first table returned from a dataset into whatever table you specify (the field names have to match exactly). Then I display the temp table in the FE.
Not elegant, but it works.
Richard
Accepting all invitesViewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply