Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

Crystal SQL updating Timberline

I am working on a procedure where Crystal SQL will update some custom fields I have in my Timberline Database.  I know that the fields can be updated through ODBC, because I can access them through a Linked Access table and modify the data.  However, when I try to do an update with Crystal SQL I get an "Access Violation," which means to me that Timberline is not allowing the update to occur.

1) Can Crystal SQL Designer do updates.

2) If so, why am I getting the Access Violation error when I try to update?

Thanks a ton!
0
Uziel
Asked:
Uziel
  • 7
  • 6
1 Solution
 
DRRYAN3Commented:
Crystal SQL Designer is a QUERY only tool.  From the help file for designer:

The SQL Designer has two primary uses:

1.     Designing and developing data sets for building reports in Seagate Crystal Reports, and

2.     Retrieving and analyzing current information on an "as needed" basis to facilitate informed decision making.

If you check the SQL syntax options, there is no UPDATE or INSERT keyword, only a SELECT.

DRRYAN3
0
 
UzielAuthor Commented:
OK, then what tool should I use to do the update?
0
 
DRRYAN3Commented:
You can try creating a linked ODBC table using MS Access to see if Timberline allows updates to its data.  

1. In Access 97, create a new, blank database
2. Click on the File->Get External Data->Link Tables
3. In the dialog that appears, change the value of "Files of Type" to ODBC and wait for the list of ODBC data sources to appear
4. Select the DSN for your Timberline data
5. Select a table or tables to link.

If Timberline does not allow updates through ODBC, you will see the message in the status bar, "This recordset is not updateable".

If it does allow changes, you can use any ODBC compliant data manager or language, such as MS Access or Visual Basic or Delphi or Visual C to make your changes.

Most accounting programs will prohibit you from making any changes to the data to protect the integrity of the data.

Do you know the file format for the Timberline data files?

DRRYAN3
0
[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 
UzielAuthor Commented:
Timberline uses Pevasive SQL.  I am very familiar with using ODBC, however the problem is this:

Timberline has 3 table types: 1) Current, 2) New, and 3) History.  The tables are identical with different uses.  The Current file holds all the data you use regularly.  The New file is for Data that has not been posted to the Current file yet.  This allow you to make changes before you post.  The History file is a storage place to move Current data to when you are done with it, and access it infrequently.

I am working on a process that will allow me to gather data from the three tables, sum it based on certain criteria (ie account number) and store the summation in a different file.  This will allow me quick retrieval of relevant data.  In order to do this I have to do a union query for all three.  Access does not handle Unions very well.  I have a nice Crystal query that gives me everything I need, but I can not update Timberline with Crystal, so I need to know what program is best for this type of database manipulation.

Thanks. :)
0
 
DRRYAN3Commented:
I want to make sure I understand.  You want to combine the data from the 3 identically structured tables in Timberline into a single table for reporting purposes which will include history, current and new data.  Is that accurate?

If so, can't you make use of the Pervasive SQL engine to perform your queries?  I'm not familiar with Pervasive, but you ought to be able to define some server side views or stored procedures which will return a valid recordset through ODBC containing your UNIONed data which you can report from.  If not, or if the Timberline distribution doesn't include the Pervasive server tools needed, you really don't have much choice but to use something like Access or VB to perform your union queries in advance of your report processing.

DRRYAN3
0
 
UzielAuthor Commented:
You are correct in what I want to do.  The question is what tool do I use to do it.  Access can not do it.  I need some kind of SQL tool to do it.  I have a SQL query that pulls what I want, but Crystal can not perform updates.  So what Can?

Here is the code if you care:

SELECT
  CURRENT_GLT_TRANSACTION."Job",
  CURRENT_GLT_TRANSACTION."Account",
  SUM (CURRENT_GLT_TRANSACTION."Debit"+CURRENT_GLT_TRANSACTION."Credit") as Balance
FROM
  "CURRENT_GLT_TRANSACTION" CURRENT_GLT_TRANSACTION
WHERE
  CURRENT_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  CURRENT_GLT_TRANSACTION."Job",
  CURRENT_GLT_TRANSACTION."Account"

UNION

SELECT
  History_GLT_TRANSACTION."Job",
  History_GLT_TRANSACTION."Account",
  SUM (History_GLT_TRANSACTION."Debit"+History_GLT_TRANSACTION."Credit")
FROM
  "History_GLT_TRANSACTION" History_GLT_TRANSACTION
WHERE
  History_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  History_GLT_TRANSACTION."Job",
  History_GLT_TRANSACTION."Account"

UNION

SELECT
  New_GLT_TRANSACTION."Job",
  New_GLT_TRANSACTION."Account",
  SUM (New_GLT_TRANSACTION."Debit"+New_GLT_TRANSACTION."Credit")
FROM
  "New_GLT_TRANSACTION" New_GLT_TRANSACTION
WHERE
  New_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  New_GLT_TRANSACTION."Job",
  New_GLT_TRANSACTION."Account"
0
 
UzielAuthor Commented:
You are correct in what I want to do.  The question is what tool do I use to do it.  Access can not do it.  I need some kind of SQL tool to do it.  I have a SQL query that pulls what I want, but Crystal can not perform updates.  So what Can?

Here is the code if you care:

SELECT
  CURRENT_GLT_TRANSACTION."Job",
  CURRENT_GLT_TRANSACTION."Account",
  SUM (CURRENT_GLT_TRANSACTION."Debit"+CURRENT_GLT_TRANSACTION."Credit") as Balance
FROM
  "CURRENT_GLT_TRANSACTION" CURRENT_GLT_TRANSACTION
WHERE
  CURRENT_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  CURRENT_GLT_TRANSACTION."Job",
  CURRENT_GLT_TRANSACTION."Account"

UNION

SELECT
  History_GLT_TRANSACTION."Job",
  History_GLT_TRANSACTION."Account",
  SUM (History_GLT_TRANSACTION."Debit"+History_GLT_TRANSACTION."Credit")
FROM
  "History_GLT_TRANSACTION" History_GLT_TRANSACTION
WHERE
  History_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  History_GLT_TRANSACTION."Job",
  History_GLT_TRANSACTION."Account"

UNION

SELECT
  New_GLT_TRANSACTION."Job",
  New_GLT_TRANSACTION."Account",
  SUM (New_GLT_TRANSACTION."Debit"+New_GLT_TRANSACTION."Credit")
FROM
  "New_GLT_TRANSACTION" New_GLT_TRANSACTION
WHERE
  New_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  New_GLT_TRANSACTION."Job",
  New_GLT_TRANSACTION."Account"
0
 
DRRYAN3Commented:
If Access won't access your data (sorry, couldn't resist), you're probably going to have to explore using the Pervasive developers SDK to get access to your data.  Their manuals are online at http://www.pervasive.com/support/technical/online_manuals.asp, but again, I don't know all that much about which pieces of their software would be needed to be able to return a view from your query.

DRRYAN3
0
 
UzielAuthor Commented:
You are correct in what I want to do.  The question is what tool do I use to do it.  Access can not do it.  I need some kind of SQL tool to do it.  I have a SQL query that pulls what I want, but Crystal can not perform updates.  So what Can?

Here is the code if you care:

SELECT
  CURRENT_GLT_TRANSACTION."Job",
  CURRENT_GLT_TRANSACTION."Account",
  SUM (CURRENT_GLT_TRANSACTION."Debit"+CURRENT_GLT_TRANSACTION."Credit") as Balance
FROM
  "CURRENT_GLT_TRANSACTION" CURRENT_GLT_TRANSACTION
WHERE
  CURRENT_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  CURRENT_GLT_TRANSACTION."Job",
  CURRENT_GLT_TRANSACTION."Account"

UNION

SELECT
  History_GLT_TRANSACTION."Job",
  History_GLT_TRANSACTION."Account",
  SUM (History_GLT_TRANSACTION."Debit"+History_GLT_TRANSACTION."Credit")
FROM
  "History_GLT_TRANSACTION" History_GLT_TRANSACTION
WHERE
  History_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  History_GLT_TRANSACTION."Job",
  History_GLT_TRANSACTION."Account"

UNION

SELECT
  New_GLT_TRANSACTION."Job",
  New_GLT_TRANSACTION."Account",
  SUM (New_GLT_TRANSACTION."Debit"+New_GLT_TRANSACTION."Credit")
FROM
  "New_GLT_TRANSACTION" New_GLT_TRANSACTION
WHERE
  New_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  New_GLT_TRANSACTION."Job",
  New_GLT_TRANSACTION."Account"
0
 
DRRYAN3Commented:
Uziel

FYI

The multiple posts you are seeing are caused by the REFRESH function in your browser.  If you are waiting to see if something is posted, you have to leave the screen after you post a comment and then come back to the question before refreshing.
0
 
UzielAuthor Commented:
You are correct in what I want to do.  The question is what tool do I use to do it.  Access can not do it.  I need some kind of SQL tool to do it.  I have a SQL query that pulls what I want, but Crystal can not perform updates.  So what Can?

Here is the code if you care:

SELECT
  CURRENT_GLT_TRANSACTION."Job",
  CURRENT_GLT_TRANSACTION."Account",
  SUM (CURRENT_GLT_TRANSACTION."Debit"+CURRENT_GLT_TRANSACTION."Credit") as Balance
FROM
  "CURRENT_GLT_TRANSACTION" CURRENT_GLT_TRANSACTION
WHERE
  CURRENT_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  CURRENT_GLT_TRANSACTION."Job",
  CURRENT_GLT_TRANSACTION."Account"

UNION

SELECT
  History_GLT_TRANSACTION."Job",
  History_GLT_TRANSACTION."Account",
  SUM (History_GLT_TRANSACTION."Debit"+History_GLT_TRANSACTION."Credit")
FROM
  "History_GLT_TRANSACTION" History_GLT_TRANSACTION
WHERE
  History_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  History_GLT_TRANSACTION."Job",
  History_GLT_TRANSACTION."Account"

UNION

SELECT
  New_GLT_TRANSACTION."Job",
  New_GLT_TRANSACTION."Account",
  SUM (New_GLT_TRANSACTION."Debit"+New_GLT_TRANSACTION."Credit")
FROM
  "New_GLT_TRANSACTION" New_GLT_TRANSACTION
WHERE
  New_GLT_TRANSACTION."Account" in ('100-00-3205.00', '100-00-3210.00', '100-00-3215.00', '100-00-1510.00', '100-00-1511.00', '100-00-1515.00', '100-00-1520.00', '100-00-1525.00')
GROUP BY
  New_GLT_TRANSACTION."Job",
  New_GLT_TRANSACTION."Account"
0
 
DRRYAN3Commented:
This question has been open for some time now.  Please return and either
1.  Accept an answer
2.  Post a comment stating that you need more information
3.  Ask Community Support to delete the question if you feel the information here has no value to you or anyone else.
4.  Ask Community Support to PAQ the question because your question was not answered but you feel the information here may be of some value to others.

Community Support is here:  http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt and you can leave a message by posting a zero point question.  Be sure to reference the question ID, found in the Address bar of your browser.

Thanks
DRRYAN3
0
 
UzielAuthor Commented:
You're right, sorry for the wait, and thanks for the help.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now