I'm trying to get a form in my code library database to work where a ListBox on the form has its RowSource property set to SQL that references a table in the FE database.

What I've tried (while in my FE database which references my code library) is letting this code library form load into memory (Form_Load runs) and then after it is loaded I run some code that sets the RowSource property to a SQL statement that references tables in my FE database. But I can't get the data to load into the form.

The reason I want to do this is that I have a re-usable form. This form would reference tables of an identical structure in whatever FE db is currently being used (the tables containing data specific to the current FE db). Has anyone here tackled such a feat? Thanks for any expert insights.

Hey Rob! ---

... Hmmm .... sounds intriging! ...so ... I am working on it .... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/compute.gif) ....

First question, how do you open the form? .. if its with a SUB, then change it to a function that returns a Form object, then you have access to all the form properties and controls...

Here are my thoughts that I will be persuing ...
- Have the function called that opens the form return a Form ojbect

Then either set ...
- the row source with an SQL containing an IN clause
- the .Recordset property of the list box

.... I have never done what you ask ... but I am working on it now ... persuing those thoughts!

I appreciate your interest in the finer aspects of Access! You are one of the best and I've done a lot of reading. I have indeed tried to call a proc in my code library which receives a form ref and then sets that form ref to an instantiated instance of the form of interest. Once the client mdb gets its handle back on the form ref, it calls a Public method in the form that sets the ListBox's RowSource property to be SQL that in turn references the name of a linked table in the client mdb. The actual table that the client links to is in a BE db (different from the code library db). I don't suspect that the linked table is of consequence--just trying to provide complete info. Unfortunately, I find that it does not work.

If we fail to get this to work, I have a work-around in mind; but I don't want to short-circuit any genius that you may get to materialize. I know that in code modules within a code library I can reference tables in the FE and create recordsets (ADO in my case) and iterate through them and set the RowSource property of a List/ComboBox to be a string that I've built off of the RS. I'd rather set a RowSource property to SQL string of course instead of building a temporary String for it.

I have made two methods work ...

Heres the details ...
Library database: ListBoxReference.mdb
Form named: frmGenericListBox
List box control name: lstGenericListBox
ColumnCount: 1
Bound Column: 1

{note: I programatically change the columnn count and bound column with out issue}

Code in ListBoxReference that opens the generic list box


Front End: Access 2003
Back End: SQL Server
Data that populates list box: tblAreas

First method (using the linked table from the FE)
---------------------------------------------------------



Second Method ... bind the list box directly to the backend
----------------------------------------------------------------------



Hope that helps!!

Altertenative to method two ... (prevent haveing to use a fixed connect string in the SQL)




Method 4 ... and ADO recordset ... (pretty much the same as method1 ...)

Thanks Brent! I follow your two approaches. I could handle either of them but the first is cleaner/easier to me. To date I've not used a DAO ref in my FE but I just recently had to set the DAO ref in my code library to enable me to change an existing field from "not nullable" to "nullable" (ADOX does not succeeed at this) (I formerly tried to restrict myself to ADO-only solutions--to my detriment). I may as well be open to setting a ref to DAO on my FE's. But first I will try your first approach with the ADO equivalent. If it fails, I'll use DAO. Will update this post when I'm done. I so much appreciate what you've done for me here! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/notworthy.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/cool.gif)

OK, I went ahead and posted even though I got a notice that you had posted.

Great! You did it and even cleaner! I'll run with the ADO one since this is my strong-suit. I just wonder why (prior to benefitting from your solutions) when I used straight SQL that referred to the linked table on the FE that Access does not make use of that linked table's underlying connection string information and successfully make the connection and get the data. Oh well, can't complain, Access has a lot of great stuff--can't ask for everything.

Thanks Brent!

>> I so much appreciate what you've done for me here! <<

You are most welcome! ... I did notice that with the ADO equivalent, the list box did not adhere to my .ColumnCount/Widths command lines .... ???? .... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/shrug.gif) .. so my list box just show the list of ID's and not the text from the second column.

PS ... you still don't need to set the DAO ref ... you can use the SQL statement method ... or ... you can utilize the fact that Access will keep a HIDDEN ref to DAO, even if you don't specify it ...



....

On another note ... Nullable? ... I would think you could use an ALTER TABLE statement executed through and ADO connection to accomplish the task.

Oh yeah. I had knowledge of that hidden DAO ref thing and forgot that it would apply here. Well, sounds like that's my ticket. Did I say that you're great! Until next time...

Missed the "ALTER TABLE" idea thru ADO conn object. I'll try to verify this.

Actually I'm trying to get my post count up. Just kidding. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/laugh.gif)

>> Alter Table <<


Also ... check out
http://msdn2.microsoft.com/en-us/library/a...office.10).aspx
Specifically tables 10 and 11 and the ADOX properties of "Nullable" and "Jet OLEDB:Allow Zero Length"

>> I just wonder why ... when I used straight SQL that referred to the linked table on the FE that Access does not make use of that linked table's underlying connection string information and successfully make the connection and get the data <<

The form, although visible in your FE ... I beleive the is basically still "bound" and is using the CodeDb instead of CurrentDb to resolve its recordsource when a "Normal" SQL is used ... create a table in your ref db and use code like this attached to two buttons on your called form:



The first returns the recordcount from the FE ... the second returns the recordcount from the referenceced db...

>> Until next time... <<

You are most welcome! ... (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)

>> Actually I'm trying to get my post count up. Just kidding. <<

(IMG:http://www.utteraccess.com/forum/style_emoticons/default/smirk.gif) .... me too!

.
.
.

(kidding as well!)

Edited by: datAdrenaline on Fri Feb 1 17:52:39 EST 2008.

>>On another note ... Nullable? ... I would think you could use an ALTER TABLE statement executed through and ADO connection to accomplish the task.<<

I was able to verify that you are right on this. Your sample code for the reader:

Public Sub MakeRequired()
Dim strSQL As String
strSQL = "ALTER TABLE [table1] ALTER COLUMN SomeText varchar(20) NOT NULL"
CurrentProject.Connection.Execute strSQL, , adCmdText
End Sub

Oddly, this MS link (http://support.microsoft.com/kb/180841) states something to the contrary (which is apparently not totally correct):
***********
The ALTER TABLE DDL Statement
Microsoft Access DDL supports the ALTER TABLE DDL statement. This statement is useful when you need to remove or add a field to an existing table.

Note: This statement won't let you alter an existing field in an Access table (for example, to change the field's data type).
***********

Other topic...
>>The form, although visible in your FE ... I beleive the is basically still "bound" and is using the CodeDb instead of CurrentDb to resolve its recordsource when a "Normal" SQL is used<<

I agree that this must be true. It is odd, however, that no error occurs when the form from the code library db is referencing tables that do not exist in CodeDB. The form is just opened with an empty ListBox.

Other...
I still need to apply one of your above solutions to get this "code library form" to work; will let you know. Thanks, Rob

Hey Rob ...

Quick note ...

>> states something to the contrary (which is apparently not totally correct): <<

Well not entirely ... the capability of a DDL SQL statement is dependant upon what object model is executing it. The DAO object model does not support the full capability of JET 4.0, however, the ADO object model does ...

So ... I beleive the link you point to IS correct ... if you are executing through the DAO object model, for example:

CurrentDb.Execute strSQL, dbFailOnError

However, as we have seen, the link is incorrect if you are executing through the ADO object model, for example:

CurrentProject.Connection.Execute strSQL, ,adCmdText

...

Check out ...
http://msdn2.microsoft.com/en-us/library/a...office.10).aspx

In the capability grid, you will see that ADO knows JET 4.0 SQL syntax, and DAO does not

...
Amazing eh? ... especially since DAO is the preferred Access object model ... even in A2007 the same behavior is experienced!

Hi Brent,

>>the capability of a DDL SQL statement is dependant upon what object model is executing it<<

I suspected this was the case. Thanks for the comments regarding the link.



Well, I got it to work. To re-cap, I have a form in my code library. This library is referenced by multiple FE mdb's. The tables that the shared form uses are contained in the FE mdbs and are of identical structure but contain data specific to the FE mdb in which they reside. Furthermore, these FE tables are actually linked tables that physically reside in different BE mdb's.

Now on the method I chose for connecting my ListBoxes to the FE linked tables. The question becomes using .RowSource vs using .Recordset for the List/Combo Box controls on the shared Form. I had success using both techniques. But it became undesirable for me to use the .Recordset property of the ListBox because the SQL for it could not reference a different driver-control on the Form. The opened Recordset that would be assigned to the Control's Recordset property would become outdated and have to be re-set once the driver-control's value changed. I didn't want to add in the plumbing to support this.

I found that using .RowSource was more straight-forward and allowed more flexibility. I used this technique that you put out there:



It all worked great! Thanks Brent for your help! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/cool.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/thumbup.gif)