September 12, 2008 at 11:32 am
hi must agree about not using office97, officexp or 2003 ok.
But can you explain where you are getting stuck re dead end?
And i'll see i can help
Also to agree with previous poster MSSQL will not help poorly designed db
harvey
September 12, 2008 at 11:51 am
I have over a hundred users of Access 2003 apps on two continents; the apps run quickly and there is no reason to use anything else for these apps. I don't use linked tables but use pass through queries for everything. Pass throughs are a little more work but the resulting performance is worth the effort. Since there really are no internal tables, the apps size is about 600k.
Also if upsizing, the upsizing wizard sucks. Its much better to use SQL Server Migration Assistant from Microsoft.
Jim
September 12, 2008 at 11:54 am
I guess it depends upon how hacked the code in the Access database is whether it would be complex to migrate to an adp. I too have worked in Access and SQL Server since 1996 and have migrated numerous mdbs to adp projects with no issues. My original post was in context of the question posted in the first post. Create a new adp project with a connection to the backend SQL Server. Import all the tables from the existing mdb into SQL. Move as many select, insert, update and delete statements and/or queries in the front end to stored procedures as possible to simplify and reduce the amount of code behind the forms etc. Pre-process as much data on SQL that will populate Access reports so they are primarily used as a formatting tool. The goal is to produce a very THIN client that performs mainly as a user interface and a reporting tool. The end advantage is by moving the data and processing to SQL Server, you can later leverage that investment against any .NET or web-based interfaces that you want to develop. Again, this is an interim step that should not add a lot of complexity, but rather improve performance and eliminate many of the problems inherent with an existing Access solution.
September 12, 2008 at 12:16 pm
Whatever my frustrations were 10 years ago, I've long since forgotten. I just remember converting and then having no means to make changes, and nothing I did from that point forward helped. It was probably something hidden that wasn't widely documented at the time, that could have helped, but I couldn't find it.
However, moving forward, some things I'll need to know:
1.) What state is the database in once converted?
a.) Can you make changes to queries, code, etc.?
b.) When you open an adp file, can you look directly at the tables in the same way you can with an mdb file, or are you stuck only accessing things through the application you have in place?
2.) How does one create a "pass-through" query? I've heard about these for some time, but have yet to see/hear exactly how that differs from any other Access query...
3.) Are there any things you have to be sure you do before conversion?
Chances are, the answers to these will generate more questions...
Steve
(aka smunson)
:):):)
hs30 (9/12/2008)
hi must agree about not using office97, officexp or 2003 ok.But can you explain where you are getting stuck re dead end?
And i'll see i can help
Also to agree with previous poster MSSQL will not help poorly designed db
harvey
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 12, 2008 at 12:26 pm
I would suggest the fastest and most useful way to learn how to use an adp project would be to create a project from scratch, connect it to an existing SQL Server test database, create a stored procedure that selects from a table and then create a simple form using this stored procedure as the record source. Ditto for a report. Once you see the basic funtionality then it becomes easier how to understand the steps to migrate an exisitng mdb application.
September 12, 2008 at 12:34 pm
Ok, I'll do that, but no sp's for now - that will have to come later for technical reasons. As I absolutely detest the record navigation that Access provides by default in it's standard bound forms, that method of tying form to data has long been dead from my perspective. I'm far more concerned with what happens when you have a form with ADO code attached to it, and how that works on the adp side of things.
Steve
(aka smunson)
:):):)
gene.stebley (9/12/2008)
I would suggest the fastest and most useful way to learn how to use an adp project would be to create a project from scratch, connect it to an existing SQL Server test database, create a stored procedure that selects from a table and then create a simple form using this stored procedure as the record source. Ditto for a report. Once you see the basic funtionality then it becomes easier how to understand the steps to migrate an exisitng mdb application.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 12, 2008 at 1:17 pm
You can use ADO in much the same way you would coding a VB front end. A good reference is http://msdn.microsoft.com/en-us/library/ms675104(VS.85).aspx and also
http://msdn.microsoft.com/en-us/library/aa189885(office.10).aspx although it is Access 2000
September 12, 2008 at 1:41 pm
Ok, so I created a new project, and connected to the database, which then made all that databases tables appear, all with the same names I had them as as linked tables in my mdb file. Then I imported all my forms, queries, and reports from that mdb file. I checked the forms first, and when I tried a drop down (combo box) that's based on ADO code, it tells me the recordsource doesn't exist. The table name within Access hasn't changed from what's on the server, so I have no idea what's going on here. Any ideas on how to fix it?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 12, 2008 at 2:07 pm
Your form can have a recordsource which could be anything from a table, view or a procedure. On this form you may have multiple subforms, combo boxes, list boxes etc. Ex:your form has a recordsource of tblOrder and a subform has tblOrderItem, your cboDate may have a Row Source spGetDate etc
Check the row source and row source type in the properties window of the combo box
September 12, 2008 at 2:48 pm
Apparently, one can't have local queries in an adp file - at least that you use as a recordsource for a combo box anyway. Thus I'm going to try out migrating the queries using the SS Migration Assistant, as I had already migrated the tables. Hopefully, that will provide what I need. Any other little gotchas like that?
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 16, 2008 at 10:43 am
Wow... trivial effort, eh? I think not. I migrated my queries, of which it created only those that didn't have parameters as views, and instead of creating stored procs for those with parms, it did nothing, so there's a ton of work right there. Then, I go into the database, and find that ALL the tables in the database on the SQL Server show up in the table list within Access, and the same goes for all views and sp's. Worse yet, I find that every .OpenQuery method now has to change to the .OpenView method, and worse still, I make one of those changes, and it thinks the object doesn't exist when I try to use the command button that implements that code. Sorry, but that's not trivial, that's a major conversion effort, which looks more like it's a start over than a simple conversion. Does anyone understand how you implement this? I'm also wondering if I have to use Office 2007's Access to even deal with this problem? I've found I can't even create a new query in the .adp file because the server is SQL Server 2005, but Access is Access 2003.
Steve
(aka smunson)
:ermm::ermm::ermm:
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 16, 2008 at 10:51 am
Garbage In = Garbage Out
September 16, 2008 at 11:15 am
Gee, that sure was helpful... Remind me why I needed that?
Not everyone can know everything, and I fail to see that any of the apps I've developed over the years in the .mdb environment constitute "garbage". Apparently, some folks come to the conclusion that because that's no longer the "best practice" that somehow (by osmosis, perhaps?) that information is automatically in everyone's tool kit. Those of us that have to get things done use what we have, and find best practices when we're allowed to have the time to do so.
Seems to me that converting from .mdb to anything other than just moving the tables up to SQL server is a considerable, rather than trivial effort. Until such time as you can offer up the facts necessary to support such an effort being "trivial", might I suggest you stop offering up "garbage" instead?
Steve
(aka smunson)
:angry::angry::angry:
gene.stebley (9/16/2008)
Garbage In = Garbage Out
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 16, 2008 at 7:38 pm
You cant use the designer in Access 2003 with SQL Server 2005. Use SSMS instead, or convert to Access 2008.
September 17, 2008 at 2:25 am
We use an Access 2003 front end (adp/ade) to a SQL2000 back end with over 40 users and have no problems - each has their own copy of the front end and there are not the performance problems that an Access back end would cause. This is a business critical order processing/stock control system that is in use 5 days a week, 10 hours per day.
A well written Access front end is not a problem
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply