Open Recordset for Accpac 1

[kwagz]

Hey there

I am trying to open a recordset in .net c# for Accpac and not sure what to parse on the first two parameters.

ADODB.Recordset tmpRs = new ADODB.Recordset();
tmpRs.Fields.Append("FIELD",...);

tmpRs.Open(...); //???
tmpRs.AddNew(...)

Please help.

kwagz

 

[zemp]

If you are trying to get at the ACCPAC data DO NOT use ADO. Use the ACCPAC COMAPI views. The views are specifically designed for accessing the data, they follow your ACCPAC business logic and ensure data integrity. Yes there is a learning curve and things will be slow to start, but the savings in hours of debugging and data repair, not to mention down time, for the company, far out weights the extra time and effort.

Search for COMAPI in this forum. With .Net you will need ACCPAC 5.2. Unfortunatly I don't have any .Net examples.

zemp

 

[kwagz]

zemp

Thank you for responding. I am using XAPI to access Accpac data. Please help me with steps of going about reading data from XAPI views into recordset then print it to a file. If there are other methods of doing that, it would be highly appretiated if you would help.

Thank you
kwagz

 

[zemp]

Treat the view as a recordset. It is basically the same thing with a different name. The xAPI doesn't have some of the nice fundtionality of a recordset (Movenext, moveprevious, etc.). For that you will need to use the COMAPI which does have those. The COMAPI is basically a wrapper for the xAPI with additional tools and functionality.

To print to a file you will need to look into the some of the keywords found below.

   Open <filename> For Output Access Write As #1
   Print #1, "Cust ID: " & view.fields("IDCUST").value & vbCrLf
   Print #1, "etc." & vbCrLf
   ...
   Print #1, "etc."
   Close #1

This is code from VB but the ACCPAC VBA code should be similar (if you are trying this from a Macro).


zemp

 

[kwagz]

Hi zemp

I am still using xAPI views to get data from accpac and treating it as a recordset. Now, what i'm trying to do is print view fields to a file like in the above example, but nothing is printed. I can see the number of records from the recordset, though.

Do you have an idea of why does it return empty field values?

Thanx
kwagz

 

[jayconverse]

Are you using the .Browse and .Fetch methods? Maybe you should show us your code.

Jay Converse
IT Director
Systemlink, Inc.

 

[kwagz]

This function returns a recordset from Accpac:

public xapiView GetItemPrice(string UsrID,string PwdID,string CompName)
{
xapiSession cSession = new xapiSession();
xapiView cView = new xapiView();
cSession.Open(strUsr, strPwd, strCo, Date, 0);

cView = (xapiView) cSession.OpenView("IC0290", "IC");

return cView;
}

Then I get to call GetItemPrice function from a function that calls PrintToFile, where the recordset to be printed is parsed.

private void PrintFile(xapiView cView)
{
FilePath = "C:/file.csv";
System.IO.StreamWriter csvFile = new
System.IO.StreamWriter(FilePath, true);
strLine = "ITEMNO, LOCATION, RECENTCOST";
csvFile.WriteLine(strLine);

strLine = cView.Fields.Item("ITEMNO").Value +","+
cView.Fields.Item("LOCATION").Value +","+
cView.Fields.Item("RECENTCOST").Value;
csvFile.WriteLine(strLine);

csvFile.Close();
cView = null;
}

This is how I print the XAPI view recordset, but no data is printed. I am not using any .Browse nor .Fetch methods.

Your comments are highly appretiated.

- kwagz -

 

[jayconverse]

That's your problem! You've opened the view, but you're not browsing it. You need a

.Browse "", True

and loop with .Fetch while .Fetch = True

Jay Converse
IT Director
Systemlink, Inc.

 

[zemp]

You need to use the .Browse and .Fetch. That tandem is what actually fills the view with the requested data and allows you to see then. Something like this.

view.init 'initializes the view to default values.
view.order=0 'order the view by the first indexed field.
view.browse "", true 'no filter, return all records
                     'True: return records in forward order
  'fetch retuns true if record exists and moves to the 
  'next logical record in the view
do while view.fetch  
   ... 'do whatever
loop

zemp

 

[kwagz]

This is the code for printing to a file, and still not getting output. Am I missing something else here.

FilePath = "C:/file.csv";
System.IO.StreamWriter csvFile = new
System.IO.StreamWriter(FilePath, true);

cView.Init();
cView.Order = 0;
cView.Browse("", 0);

while(cView.Fetch().Equals(true))
{
MessageBox.Show("Inserting Data");
strLine = cView.Fields.Item("ITEMNO").Value +","+
cView.Fields.Item("LOCATION").Value +","+
cView.Fields.Item("RECENTCOST").Value;

csvFile.WriteLine(strLine);
}

I am coding in .NET C# and its quite different from VB6. What I wanted to do initially was to get a XAPI view and put it in ADO recordset. I can create a disconnected recordset and append the fields to it, but I was having difficulty in opening ADO recordset afterwards because it wants an ActiveConnection object as a parameter that I dont have or dont know what to put there. I tried parsing an open session as an ActiveConnection but returns an error.

From there I was gonna print an ADO recordset to a file because it has functionalities like MoveNext to go through the records. Is there another way of doing this using XAPI views.

Thank you
kwagz

 

[jayconverse]

I think you need to get past using the XAPI. The COMAPI has a .Movenext method, as well as .Gotop, .Gobottom, etc.

Jay Converse
IT Director
Systemlink, Inc.

 

[zemp]

I am not sure of the syntax for using ACCPAC objects with .Net. However I believe that only the COMAPI from AAS 5.2 and up is compatible with the .Net CLR. Non compatible xAPI may be giving you the problems.

You can also record a VBA COMAPI macro to get the order of commands and the views and view compositions that you require. Then just translate that blueprint into your C# syntax.

zemp

 

[kwagz]

Well, I think I'll go the COMAPI route. How do I get data into the view, and use the .MoveNext to loop through it?

This is my AccpacCOMAPI code:

AccpacSession cSession;
AccpacDBLink dbLink;
AccpacDBLink sysLink;
AccpacView cView = null;
AccpacViewField cViewFld;

cSession = new AccpacSession();
cSession.Open(strUsr, strPwd, strCo, Date, 0, "");

dbLink = cSession.OpenDBLink (tagDBLinkTypeEnum.DBLINK_COMPANY, tagDBLinkFlagsEnum.DBLINK_FLG_READWRITE);

sysLink = cSession.OpenDBLink(tagDBLinkTypeEnum.DBLINK_SYSTEM, tagDBLinkFlagsEnum.DBLINK_FLG_READWRITE);

dbLink.OpenView("IC0290", out cView);

.Net doesn't seem to support most functionalities that are in VB6, hey.

If there are other Threads that you think might help me, please forward their numbers to me.

Your comments and advices are of great help to me.

Thank you
kwagz