November 1, 2003 at 10:33 am
New to this list and wondering if I can get some assistance or advice on a particular issue? I was informed that the acting DBA for a small company I support walked off without finishing a project and now the company's in a jam to get finished. The databses were created in Access and now need to be converted to MSSQL 2000. From what I've been reading I can run the Upsize wizard in Access to get the database converted but I'm thinking there's more to this. Can anyone confirm? Better yet would anyone be available to assist me with this endeavor?
TIA
Just Do It...
Just Do It...
November 1, 2003 at 7:57 pm
Hi,
Yeah, The answer is, 'It depends'! It all comes down to how this guy wrote the application in the first place. If your lucky he did it with the form wizard,bound forms and very little VBA. Your going to have to do a bit of fishing about in his code before you can tell the extent of the problem.
The upsize wizard is pretty good at getting your tables moved across to SQL. Potential problems are not having all the latest service packs giving an 'overflow' error, and watch out for autonumbers that loose their identity setting.
HTH
Chris
November 1, 2003 at 9:35 pm
Thanks. I managed to get all of the databases converted over to sql but I'm not having any luck viewing any of the forms. This really isn't my area of expertise so I'm searching for a tool or way to view the forms using sql. Is there some app available that'll do that for me or do I have to create something from scratch?
Thanks
Just Do It...
Just Do It...
November 2, 2003 at 6:01 pm
Hi,
Not sure what you mean by 'view the forms using SQL'? The upsize wizard should create an access project for you (.adp file). It is this access project that has the forms in it. These forms need to point to the data stored in your SQL database. If the upsize wizard hasn't altered the recordsource in each form you will need to go in manually and alter this. This should then work OK if the forms were bound-forms and had no data-accessing VBA code. If the programmer chose to code all the data access in the VBA as ADO or worse DAO you will need to go through all the code line by line to convert it manually. I don't know if there is a 3rd party tool that could help with this, perhaps someone else does? I think it would be very difficult to write such an app because you do not have a firm structure for any custom code that could be added.
November 3, 2003 at 7:17 am
First, check to see (in the original Application .MDB) if the Form DataSource is bound to a Table directly or a Query. If it is bound to a Query, then the Upsizing Wizard failed to convert the Query into a View in the .ADP version of the App. Also as the previous user stated, check to see if the Forms get is datasource from an ADO/VBA Code or not, if it does then verify that the code is pointing to the correct tables. You may also have to rename the Table Names, because some time when you use the Upsizing Wizard to convert an .MDB File into a .ADP File, depending on the user name used to log in to SQL Server the table names may change. Example a Table Named "tbl_Customers" may change to "username.tbl_Customers" with this, the Form will still be referencing table name "tbl_Customers" eventhough the new name is "username.tbl_Customers" Check this.
November 3, 2003 at 7:52 am
Yes, they are right. On the forms that don't work, check the recordsouce (in Properties).
You might also verify that the queries were upsized correctly. Down the line you might take the time to write a few as stored procedures instead of views.
bonne chance
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
November 3, 2003 at 8:29 am
If you want to discuss any specific challenges, you can send me a note off forum - art@sichun.com
Good luck!
November 5, 2003 at 2:52 am
these are some helpful notes for you
1- use upsizing wizard from XP (access 2002) .. because It transfer data and relationships more accurate
2- relink tables by using ODBC .. by making a System DSN on every machine ...
30 rename the tables to its old name by removing "dbo." from the begining of evry table
and forms will work correctly after that.
I hope this help you ..
Alamir Mohamed
Alamir Mohamed
Alamir_mohamed@yahoo.com
November 8, 2003 at 8:15 am
Don't forget to check the report file produced by the upsizing wizard. It will contain a lot of information on what it failed to do, such as queries that failed to be upsized. In fact this is the first place to start any modifications.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy