September 17, 2008 at 3:13 pm
I now have an interesting problem with an Access 2003 adp file, based on my testing of the supposedly trivial conversion. My tables are all in a particular schema, along with all the queries being views, however... it appears that adding a command button to a form to do the same thing that in an .mdb file, just meant running a query, is now impossible. If I create the command button from scratch, it allows me to select the query, but then tells me it gets an ADO error, indicating the object may not be valid for this operation. What does that mean? Then I take the prefixed database role out of the name and it tells me that it can't even find the object. If anyone has any clues, please let me know. Thanks!
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 17, 2008 at 3:56 pm
The only remotly close problem I had to yours is when the user didn't have permission to execute the procedure or function or whatever else. I got a general ADO error like yours that didn't give out any usefull info.
If that's not it, you can always run profiler and capture what access is sending to sql server, paste it in SSMS and try to figure out what's wrong with the statment that access creates (happens a lot more than it should!).
September 30, 2008 at 12:44 pm
smunson (9/12/2008)
Hi All,However, Access can be no picnic too. It has it's own quirks, including the tendency to self-corrupt, especially if you don't run a regular "Compact and Repair". Perhaps the best way to use Access is when you store the data in tables on a SQL Server, as that kind of scenario can really rock. I've also got high praise for MS's migration tool for going from Access to SQL 2005 or SQL 2008. It beats the upsizing wizard HANDS DOWN. See the Microsoft web site to find that FREE tool, and you'll be VERY glad you did. Because you can develop an application rather quickly in Access, then use that tool to move the tables up to SQL Server (including having the tool link the tables for you), you can get a darned good app out the door and with some serious robustness surprisingly quickly.
Steve, are you referring to the SQL Server Migration Assistant? I agree with our assessment. The SSMA alerted me to which queries would fail or succeed in a report like format. I didn't like how it rendered the reports in HTML fashion however. It was better to copy and past the results in a separate .txt doc. I performed the SSMA from a Access 2k3 -> SQL 2k5
Also, how often should a Compact and Repair be performed on an Access DB? We are in such a DB between 5 people about 20 times a day 5 days a week, with each locking it up about once a day.
[Given my current situation (coming to the end of a contract with limited local prospects), if there's anyone out there that needs help with Access or Crystal or SQL or a combination thereof (that goes beyond what you can get help with here), and can handle me working remotely via VPN (unless you're local to the Grand Rapids, MI area) on nights/weekends (for now, anyway), please contact me via PM.
Have you been successful with the freelance arragment you have proposed? What kind of work have companies brought to you? Why are using Crystal Reports as opposed to something more akin to ASP.NET? Thanks
[Steve
(aka smunson)
:):):)
September 30, 2008 at 12:52 pm
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.
I'm trying to learn about stored procedures and like your suggestion. Would you store the stored procedure on the Access DB or SQL DB? Also, if I wrote a simple stored procedure, say: "Select * FROM MyDB", how is this rendered as a "stored procedure". I don't understand the way stored procedures are rendered as a reports. I understand how one can embed a SQL Data object in an ASP.NET application (I've done this), but how would I do with a SP? Thanks.
September 30, 2008 at 2:26 pm
Yes, I'm referring to the SQL Server Migration Assistant. I never bothered to migrate anything other than Tables and Queries, and because of the problems on the query side, I have yet to do anything with the migrated queries (which are now stored procs). Thus I haven't moved over from .mdb file to .adp either. I didn't think that SSMA migrates reports...
Compact and Repair frequency depends on usage, and at 5 users in and out 20 times a day, it sounds like you may be at the point where a .mdb file starts to reach it's limitations, especially if it's locking up on a daily basis. There could be other reasons for that, such as slow network, slow SQL server, but it's more likely that you're just running into too much activity for the .mdb file, and it's choking on volume. At the activity level you've stated, and based on the lockups you're running into, I'd be doing it once at lunchtime, and again at the end of the business day, and doing backups immediately before each occurrence. Yes, it's a pain...
On the freelance side, I've had no one contact me as yet... at least none as a result of this website, anyway. I would love to have had some contact, but take a good look at the economy, and one is not terribly surprised.
I use Crystal Reports because of the relative ease of deployment, and the relatively feature-rich formatting environment it provides, but I always develop my query first, and then insert it as a command rather than using Crystal's ability to create the query for you. Crystal makes rather disastrous join decisions at times. Using ASP.NET would entail an entire additional layer of infrastructure that isn't always present.
Steve
(aka smunson)
:):):)
P.S. My current contract is probably done on 10/17/08.
umanpowered (9/30/2008)
smunson (9/12/2008)
Hi All,However, Access can be no picnic too. It has it's own quirks, including the tendency to self-corrupt, especially if you don't run a regular "Compact and Repair". Perhaps the best way to use Access is when you store the data in tables on a SQL Server, as that kind of scenario can really rock. I've also got high praise for MS's migration tool for going from Access to SQL 2005 or SQL 2008. It beats the upsizing wizard HANDS DOWN. See the Microsoft web site to find that FREE tool, and you'll be VERY glad you did. Because you can develop an application rather quickly in Access, then use that tool to move the tables up to SQL Server (including having the tool link the tables for you), you can get a darned good app out the door and with some serious robustness surprisingly quickly.
Steve, are you referring to the SQL Server Migration Assistant? I agree with our assessment. The SSMA alerted me to which queries would fail or succeed in a report like format. I didn't like how it rendered the reports in HTML fashion however. It was better to copy and past the results in a separate .txt doc. I performed the SSMA from a Access 2k3 -> SQL 2k5
Also, how often should a Compact and Repair be performed on an Access DB? We are in such a DB between 5 people about 20 times a day 5 days a week, with each locking it up about once a day.
[Given my current situation (coming to the end of a contract with limited local prospects), if there's anyone out there that needs help with Access or Crystal or SQL or a combination thereof (that goes beyond what you can get help with here), and can handle me working remotely via VPN (unless you're local to the Grand Rapids, MI area) on nights/weekends (for now, anyway), please contact me via PM.
Have you been successful with the freelance arragment you have proposed? What kind of work have companies brought to you? Why are using Crystal Reports as opposed to something more akin to ASP.NET? Thanks
[Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 46 through 49 (of 49 total)
You must be logged in to reply to this topic. Login to reply