Hi,

I'm trying to find a way to make quick query against Adagio's ODBC Connection the same wayI do it in SQLServer Management Studio.

I know I can do it in Visual Studio but is there an easy way.
There is something in SQL Server Management Studio to add linked servers but I do not know what the Datasource, Provider String, Location and Catalog field should contain...

Any insight would be nice.

I understand that the ODBC Module has the ability to connect to SQL Server but I'm not sure what this functionality allows.

Is there more documentation available than the PDF that shows how to create a DNS?


Thanks
Yann

Have a look at AdagioODBCv2.chm

Let me know if you feel that this does not properly document setting up a DSN to connect to the Adagio tables.

Hi Michael,

the DSN is setup. I had no issue there.

I'm just having issues figuring out what tables to use for AP and AR.

It was also not clear how to setup the SAM files. I have quiet a few of them...
I think I have the right one now... I found one 3 inside the company directory. One for AR, another for AP and a GL one.

Are there specific table that I should start with?
Customer Master and Vendor Master look like a good place to start. is there a way to figure out what table is related to what Adagio screen? Also, there are table for older versions and newer version of the software. How do i figure which one to use?

What is the underlaying database that adagio uses?

Thanks
Yann

At the moment i'm looking for transaction history to calculate how much we have purchased from our us vendors in the last 5 years.

my DNS is pointing to

The dictionary exporter seems to suggest that I should use the AP Transaction History.
In crystal, I see APTransactions_ap81btm but there does not seem to be anything in there.

There are other tables with History in their name but I haven't found the right one that correspond to the Historical Transactions from the transactions tab in Payables 8.1E

Hi Yann:

The transaction History table is "APTransactionHistory_ap81bhst". But it's odd that the table you picked would be empty. That is the transaction table for all Current (unpaid) transactions. Unless your Payables department has paid all invoices and just run Period End, you would expect to find something in there. Check what the History table shows and let us know.

Vendor documents are stored in two files. APTransactions_ap81btrn and APTransactionHistory_ap81bhst. The latter would just contain completed transactions. The former would be a mix of paid and unpaid transactions.

Receivables is different. For AR9, all the customer transactions are stored in one file. ARTransactions_ar90atrn.

Hi Bruce.

Here is what I se when I add that table...
There are not fields in it

you can view the image here but your the adagio Forum does not support these linked images...

[img]https://lh6.googleusercontent.com/EXGztC...7bv0=w1448-h729[/img]

and Yes, there is data in the APTransactions_ap81btrn table...
Why does the database definition table exporter not refer to these table names?

A short description somewhere of what is in these table could also be useful but once I play with it, I guess I'll figure it out.

Yann:

When you create the DSN, one of the options is "Table names" and there are 2 options "Long" and "Short". If you choose "Short" the table names will more closely match those located in the Data Dictionary in Excel.

Use the DataDictionary Exporter with /ODBC on the command line to see the ODBC names for the fields.

I tried this but no luck...

Like so?
G:\SOFTRAK\system\DataDictionaryExporter.exe /ODBC

I tried both but I thought the names were easier to figure out when they were long...
But if i reference the Exported Definition and the ADS Alias Column on the first Worksheet, I guess it makes sense.

Any idea why that table would be empty?

What is the Column "all"vs common do?

Thanks
Yann

Have a look at AdagioODBCv2.chm, Setting Up a DSN
Columns:

Common - Does not show 'hidden' fields in the table and translates coded values. Fields with coded values will have a "_T" suffix added to the field name. 'Translated coded values' should not be used in the WHERE or JOIN clauses of an SQL statement.

For example, WHERE SRTransactions_as80aitm.Type_T="Tax" will not work properly.

Instead, use the All option described below and WHERE SRTransactions_as80aitm.Type="T".

All - Shows the hidden fields and does not translate coded values.

I still haven't figured out why the some tables are empty

See screen shot:
[img]https://drive.google.com/file/d/0B7qAWeNhnmSucnJhY3RvOXJKbUk/view?usp=sharing
[/img]

I'd recommend that you confirm that your APHIST'R data file is greater than 512 bytes. Confirm you are pointing to that data. Then try it without any joins or filters.

It's 11 MB

APHIST'1.DAT is 2.5 MB
APHIST'2.DAT is 3.4 MB

I changed my ODBC DSN to use short names and common Columns...
I created a new crystal report after restarting Crystal (just to make sure it was not a crystal issue). I added the AP81BHST Table and it would not allow me to view any rows that would be inside of it...

if I add the AP81BMTC table, I can see all the columns that exist in the table...

What could be wrong here? Permission?

Thanks
Yann

Hello Yann,

Since you have GridView, do the following:

• Launch GridView pointing to your Account Payable data
• Create a new View from Adagio Payables
• Select the A/P Transaction History Table
• Select some fields from the table
• Don't create a Filter
• See if anything is displayed.

This will tell you whether there is anything in the file.

Then, create a DSN using either the 32 or 64 bit Administrator, depending on the version of Excel you are using. Place "AP*" in the "Table Selection" field and save the DSN. Use long names.

Launch Excel, Choose Data | From other sources. and select your DSN. Choose AP Transaction history from the list of tables. Make sure the table can be loaded into Excel. This will show whether the connection is working properly.

I just went through all these steps with the sample data on my system with Excel 64 bit and everything worked fine.

I then launched Crystal Reports 8.5 and select ODBC as my data source. The Adagio AP one was displayed. I chose the Vendor Master and Transaction History tables to add.

Worked fine:

If you zip the AP data that your DSN or Connection string points to and upload to www.softrak.com/dbr attention Michael I will have a look to make sure that the data is OK. Create a File DSN that is a copy of your System/User DSN and include it with the AP data.

I always suggest that you use Excel | Data | From Other Sources | From Microsoft Query to test your DSN. Looking at the table with Gridview will let you know what data is in the table. ODBC field names are built from Gridview field names with spaces and "special" characters removed.

Hi Andrew,

ok, i found data in the A/P Transaction History via GridView but when I do it through Excel, I get unable to obtain list of tables from the data source.

I'm using 64 bit excel and the 64 bit ODBC the way you suggested it...

Thanks
Yann

Here is another error I'm getting after using the AP* in the table selector...

ODBC Error in Crystal

Please post a screen shot of the DSN you are using as well as a screen shot from the properties tab of AdagioODBCv2.dll

The problem could be data related. You may want to connect to AP sample data to confirm your setup.

Here are the 2 screen Shots...

ODBC Properties

DNS

Hi Yann,
The ODBC Properties looks OK.
The DSN has * for the Table Selection, please change to AP81B* to minimize the tables returned to MSAccess

In an earlier screen shot of the Visual Connection Expert you show Red&Yellow pointer for the 3 keys from APTransactions_ap81btrn.
It is my understanding that these only were displayed for ODBC 8.5 and are not there ( at this time ) in ODBC 9.1

As noted, all of Yann's ODBC properties and DSN information was correct. The problem was tracked down to an issue with the shipping AdagioODBCv2.DLL (2.1.7.133) and is fixed with AdagioODBCv2.DLL (2.1.7.155) or greater which will ship with the next release.

Thanks Michael!