This an a short tutorial on how to set up and edit records in a Sage Timberline Estimating Database. There will be a separate posting on attaching to Estimate files, which is slightly different.
Audience: Advanced database users and administrators who need to make global (or minor) changes outside the normal program functions. Timberline comes with a complimentary Database Editor program, make sure you install and try to do your task there first, it is much safer. If I get any feedback from this article I can follow up with many tips and solutions for common database issues (mass changes to data, complex find/replace operations, syncing with external systems, etc).
Warning:
When you make changes to your Pervasive database through the back-end you put the integrity of your data at great risk. Make sure you have backups and are not working on a production database. Access must have exclusive access to the database during this time.
We will be using Microsoft Access 2007 to connect to our Estimating Database. This procedure could be done in any other ODBC compliant software. Microsoft Access will allow you to edit most fields in the database, minus the key fields (see further down for solutions to this).
httpv://www.youtube.com/watch?v=kJqBFbFjcuM
You can watch a video of this procedure.
Checklist to get started:
- Sage Timberline Office Estimating 9.x (with ODBC Estimating Driver)
- Microsoft Office Access 2007
- Current copy of database, copied locally to a folder on the root of drive C:
- Call the folder “estdbaccess”
Step 1 – The ODBC Connection
In order to save you some debugging time, I will start with the ODBC connection to the database.
Click on Start -> Run… (if you don’t have that press WindowsKey + R)
Then type ODBCAD32 and press enter.
A side note:
If you have a x64 (64 bit) operating system, you will need to run the ODBCAD64
Follow this link, it can help explain this.
Moving on, regardless of which flavor you choose, we will go into the system DSN tab.
Click Add…
Choose “Timberline Data Source”
Click OK
Step 2 – Create the MS Access file
Create a new MS Access database titled “estdbacces.” Place it in the folder above.
[fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]
Step 3 – Link or Import Data Tables
Next we will click the External Data tab in Access, within the Import group click More and choose ODBC Database.
[/fusion_builder_column][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]
On the list we will choose either import or link. I normally go with Link. Depending on what tasks you are following up with, you will need to decide. Importing will get you a copy of the data tables stored in the Access database. Linking will give you the ability to read and write to the live database without import/export steps.
[/fusion_builder_column][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]
Step 4 – Select Actual Tables for Import
At this point you will be presented with the master list of tables from the Estimating database. Simply click on each one you want to import/link. Click OK when done.
[/fusion_builder_column][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]
Conclusion
[/fusion_builder_column][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”]
Well here you are, now you should be able to work within Microsoft Access 2007 to make any changes to the tables you have selected. Sometimes if it gets really hairy, I will send these tables out to Excel to make even bigger changes not allowed within the data structure.
You will have to re-import the tables after that. Ask me and I will do a write up on that process.
You may notice that you cannot change the key fields in most of the Estimating tables. This is by design. It will cause issues with linked information if you do that so they turned it off. If you need to re-key your data, you will need to delete the old record and append the new one. I would in this case do an import of the data into Access then make the key changes in Access. Open a link to the real table from Timberline, append the new record from your import table, and finally delete the old record.
Thanks for reading my first blog tut.[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]
