Timberline 9.2.1 and ODBC with ASP

[ultrajim]

Hello

I'm pulling my hair out trying to make a simple ASP page that connects to my Sage Timberline Office Accounting & Property Management data. There have been some other posts, but none of those really describe my problem much.

I run Timberline on a Windows 2000 Server with SP4. We're on 9.2.1. I gave up trying to access the data from my real web server, so I installed IIS on the TL server for simplicity's sake. I run task security, and my special DSN user has unlimited rights. We don't do file security, but I checked anyway to make sure my tables were clear. My Timberline Data ODBC driver in odbcad32.exe is 9.02.55.20.

Most of the Connection Strings listed in other posts work to establish the connection. The problem I have is that the connection only brings back the COMPANY_INFORMATION table (I use the OpenSchema(20) method to loop the tables, and that's the only one that comes back.) Since none of the useful tables are present, my Recordset doesn't connect, creating either a "-Microsoft OLE DB Provider for ODBC Drivers (0x80040E37) [TimberlineODBC][TimberlineODBCEngine ODBC Driver][DRM File Library]No such table or object.-" or "-Microsoft VBScript runtime (0x800A01FB) An exception occurred: 'Open'-" error on the Recordset open.

I've tried messing with the table descriptions & max segment size in the DSN to no avail. I’ve tried using the standard names for the Tenant table I’m looking up. I've tried using the DNS-less Connectionstring with the same problem. I don't think using the Pervasive driver will work the way it will for Service Management, because of the way Timberline has its data files. (I can connect to SM just fine.)

This is what I have so far:
<%
Set cnTL = server.CreateObject("ADODB.Connection")
Set rsTL = Server.CreateObject("ADODB.Recordset")

dim sConnString, rsX0
sConnString = "DSN=TL-GCG;Uid=dsn;Pwd=dsn;CODEPAGE=1252;DictionaryMode=false;StandardMode=true"
cnTL.Open sConnString

‘^^ this part seems to work fine

rsX0 = "SELECT * FROM TENANT_PMN_TENANT"

rsTL.Open rsX0, cnTL
'^^ no soup for you! errors!
%>

Despite nearly nine grand a year for our support contract, Timberline’s support has failed to get results, because they don’t support ODBC functions beyond Word, Access, and Excel. Sage Timberline Office is the bane of my existence.

Any ideas? Thanks.

 

[mirtheil]

Try issuing a:
SELECT * FROM "X$FILE" where "XF$NAME" = 'TENANT_PMN_TENANT'
to make sure the table name is as you expect it. If you don't get any records back, change the WHERE to:
where "XF$NAME" like 'TENANT%'
That should help you find the right table name according to the ODBC driver.
Please note, the syntax I gave is for the Pervasive driver not the Timberline driver but it should work.



Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[ultrajim]

The Pervasive driver isn't showing me much love today. All it does is hang with the following ConnectionString:

<%
Set cnTL = server.CreateObject("ADODB.Connection")

sConnString = "Provider=MSDASQL.1;DRIVER={Pervasive ODBC Engine Interface};DBQ=E:\Timberline Office\Accounting\MY DATA FOLDER"

cnTL.Open sConnString
%>

I tried with & without the UID & PWD using both TL's UNs and Windows UNs. Tried with and without the Provider. I also tried the Client Interface - same thing. Any ideas?

 

[mirtheil]

First, you should use a real Database Name for the DBQ property rather than a path. What happens if you create a Client Interface DSN and use the "DSN=<DSNName>" in the connection string?
Second, make sure that the IUSR_machinname user has rights to the data, DDFs, and PVSW\BIN.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[ultrajim]

When you try to create a Pervasive Client or Engine Interface DSN in the ODBC Admin, you have to specify a Database Name. The only one I have is DEMODATA. I can't create one for Timberline because there aren't any DDF files in my company data folder. (There is a DDF folder outside of the data folder, but it doesn't contain any data; just three DDF files totalling less than 2 MB.) Everything useful for Accounting and Property Management is in the PVData folder (in my company data folder). PVData has 41 folders, and each of those has a DCT and a bunch of MKD files. Unless I'm mistaken, Timberline doesn't use the straight Pervasive structure. It must translate it somehow into these various folders. The Service Management component does use a single folder with DDF and DAT files, and that works from ASP perfectly after I create a DSN.

The moral of the story is that I don't think I can create a DSN to Timberline using the Pervasive Engine.

 

[mirtheil]

You'll need DDFs to create an ODBC DSN for the Pervasive driver. DDFs describe the data files (typically *.MKD but can be anything). It is possible (but not recommended) to have DDFs and data files in different directories.
What I would suggest would be to create an Engine DSN pointing to the DDFs and data files (it's an option when you create the DBNames to specify multiple locations).

You should be able to use the syntax I gave against the Timberline driver as well.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[jibberski]

I highly recommend using DTS to "Backup" your data to a SQL Server. Build a daily ODBC read through the DTS wizards. Make sure your PVSW ODBC settings are pointing to an accoutning folder and the proper permissions are in the registry too.

Timberline is very protective of itself. Don't be upset about it; after all it is your accounting package.

 

[klewis10367]

Just mny 2 cents...you need to use the Timberline Driver, not the pervasive.