10-16-2013 11:31 AM
The command line you are using to access the OLEDB needs to be updated so that it accesses the OLEDB2.
Stan
10-16-2013 11:43 AM
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
10-16-2013 12:09 PM
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
10-16-2013 12:12 PM
I tried it with just contact and it did not work. What is the best way to grab the whole table as you suggest?
10-16-2013 12:22 PM
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
10-16-2013 12:44 PM
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?
10-16-2013 01:21 PM
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
10-16-2013 01:48 PM
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=;"
10-16-2013 02:36 PM
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
10-16-2013 02:41 PM
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