OLE Error

[wellmet]

The command line you are using to access the OLEDB needs to be updated so that it accesses the OLEDB2.

Stan

[larry456]

Better...now I am getting undefined fields on my call

.CommandText =

"Select [contact lot],[contact contact],[contact owner1first],[contact owner2first],[contact Address 1] from Contact"

Lot is a custom field, so I tried taking it out of the select statement, but then it told me contact was undefined.

How should the select be done?

Thanks again!

Larry

[wellmet]

I don't think you need for it to be [contact contact] just [contact].  I think that column might be called fullname though.  How did you find out what they were when you wrote it the first time?  You could probably grab the whole table and enumerate through the columns.  That might be the simplest way to get the information you need.

Stan

[larry456]

I tried it with just contact and it did not work.  What is the best way to grab the whole table as you suggest?

[jimdurkin]

Fully qualify the field using TABLENAME.FIELDNAME without  brackets

SELECT CONTACT.Contact,

CONTACT.Company,
FROM CONTACT

If a field name has a space then you need to use brackets

SELECT CONTACT.Contact,
CONTACT.Company,
CONTACT.[First Name]
FROM CONTACT

MS SQL also accepts double quotes

SELECT CONTACT.Contact,
CONTACT.Company,
CONTACT."First Name"
FROM CONTACT

-- Jim Durkin

[larry456]

Closer.

.CommandText =

"Select contact.lot, contact.contact,contact.owner1first,contact.owner2first,[contact.Address 1] from Contact"

It does not recognize my custom field lot, but if I remove it and try again, it also does not recognize address1.  It apparently does recognize onwer1first, owner2first and contact.

Thoughts? 

[jimdurkin]

You need brackets around the field name only

Try this

contact.[Address 1]

Not this 

[contact.Address 1] 

Is "Lot' the column name or the display name?

You can use the ACTFieldDescriptor object to get the actual colum name.

Or you can use one of the reports in the actdiag tool .

Hope this hepls

-- Jim Durkin

[larry456]

Thank you all!  I have it working with OLE2 now. 

Another issue...back when I wrote this, I did not feel advanced enough to include my 2 tables in the ACT Instance, so I installed a seperate instance, sqlexpress, and connected to it.

How hard is it to create tables in the ACT! instance of sql server and to reference them?  The current connection is initialized as follows:

dsource = HostApplication.ActFramework.CurrentHost

　

' strConnectDbCheck = "Data Source=" & dsource & "\SQLEXPRESS;Integrated Security=SSPI;"

'  strConnect = "Data Source=" & dsource & "\SQLEXPRESS;Initial Catalog=" & DATABASE_NAME & ";Integrated Security=SSPI;"

        strConnect =

"Data Source=" & dsource & "\SQLEXPRESS;Initial Catalog=" & DATABASE_NAME & ";User ID=;Password=;"

[wellmet]

You would think that since it knows what database it's in and what table you are selecting fields from that you wouldn't need to specify the table name with the field name.  Thanks for the correction and detailed answer.

Stan

[wellmet]

Most people don't know that you can link two databases.  Nice one.

As far as adding tables to an ACT! database is concerned you probably shouldn't do it.  It used to be that if you added a table to an ACT! database other than by using the SDK that ACT! would delete it when you ran the diagnostic utilities.  It probably still does.  However you can add a table to the ACT! database as long as you assume that it won't be there in the future if you go looking for it.  In other words if you create it and or populate it every time you access it.

Stan