Solved

Crystal SQL updating Timberline

Posted on 2001-08-29
13
350 Views
Last Modified: 2008-02-01
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
Comment
Question by:Uziel
  • 7
  • 6
13 Comments
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6438467
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
 
LVL 1

Author Comment

by:Uziel
ID: 6438496
OK, then what tool should I use to do the update?
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6438758
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
 
LVL 1

Author Comment

by:Uziel
ID: 6441348
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6441393
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
 
LVL 1

Author Comment

by:Uziel
ID: 6441408
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Comment

by:Uziel
ID: 6441467
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6441477
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
 
LVL 1

Author Comment

by:Uziel
ID: 6441487
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6441509
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
 
LVL 1

Author Comment

by:Uziel
ID: 6441513
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
 
LVL 12

Accepted Solution

by:
DRRYAN3 earned 200 total points
ID: 6523841
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
 
LVL 1

Author Comment

by:Uziel
ID: 6527157
You're right, sorry for the wait, and thanks for the help.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now