Replies

Pleased to be of service!

here’s the situations: on a windows form, you have several combo boxes with standard items (such as Yes, No, N/A). In Access, you can create a lookup table and bind the control itself to some other table. In .NET there is no corresponding RowSource setting. If you use the same data source for a series of combo boxes to the same lookup table, the application will also ‘bind’ every single comb box, meaning selecting Yes in one selects Yes in all the others.

I believe this happens because .NET has a setting called ‘CurrencyManager’ that handles data binding for any instance that where it is declared, even if the data source is on multiple controls.

So, instead of just setting the controls to the same data source, you need to do something like this in the code-behind:

Use the same datasource but prevent the controls from interacting:

Me.ComboBox1.DataSource = New DataView(_MyDatabase_1DataSet.MyDataTable) <– note the keyword 'New'
Me.ComboBox2.DataSource = New DataView(_MyDatabase_1DataSet.MyDataTable) <– note the keyword 'New'

Automatic assignation:
In my case, I have many combo boxes that need to be bound in this fashion. rather than copy/paste a bunch of lines like above, I created the following loop:

Dim obj As Object
Dim cmb As ComboBox

For Each obj In MainTab.TabPages(1).Controls

With obj
If LCase(TypeName(obj)) = "combobox" Then <– you can change this to textbox or listbox, etc.

cmb = CType(obj, ComboBox) <– must do a cast to set properties for the control you want to manipulate

Dim ds As New DataView(_MyDatabase_1DataSet.MyDataTable) <– note keyword New

cmb.DataSource = ds
cmb.DisplayMember = "RangeValue" <– for combo box, you have to set the display and
cmb.ValueMember = "RangeValue" <– the stored value

End If

End With

Next

this issue has been resolved.

yeah that did it – thanks!

at present, there are 23 tables that need to be massaged. i am somewhat familiar with arrays but it’s been a while. currently i am trying to figure out how to insert the SQL into an array. It should be in the code block

If i = 0 Then
strSQL = “ALTER TABLE [” & tdf.Name & “] ADD [Confirmed How? (Code)] TEXT, [OES Notified Y/N] TEXT;”
INSERT SQL COMMAND INTO ARRAY
End If

i have thougth about option 2 but i am not sure how i would write a single SQL statement that adds rows to several tables, and i’d prefer not to write the SQL to some other table and loop thru that. but i agree that it’s probably the case that DAO doesn’t want me to manipulate a table while i have it open. i was hoping there was some way to open a recordset such that it doesn’t lock the table when i operate on the table with a tabledef.

the preferred option would be to create an in-memory array of the SQL statements and then loop thru that; i can do that in .NET but i’ve never tried it in VBA/Access. but that sounds like a useful technique in any case… i also have a routine that does a UNION select on all these puppies (the tables i mean) and i can imagine doing that in VBA as well….

well, i will explore memory array options…

i believe so. the function opens with

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field

‘ Fill collection
Set dbs = CurrentDb

then i create a table def so i can inspect the tables and only look at ones with a specific naming convention; if found instantiate a recordset to begin iterating thru the collection on the found table:

For Each tdf In dbs.TableDefs
If IsNumeric(Left(tdf.Name, 1)) = True Then
Set rst = dbs.OpenRecordset(tdf.Name, dbOpenTable)

Since i am adding new columns, i set a flag and inspect the column names so i don’t try to add the same columns twice:

If Not rst.RecordCount = 0 Then
rst.Edit
i = 0

For Each fld In tdf.Fields
If fld.Name = “OES Notified Y/N” Or fld.Name = “Confirmed How? (Code)” Then
i = 1
End If
Next fld

The fun is occurring when i try to add columns in those cases where the flag remains 0:

If i = 0 Then
strSQL = “ALTER TABLE [” & tdf.Name & “] ADD [Confirmed How? (Code)] TEXT, [OES Notified Y/N] TEXT;”
dbs.Execute strSQL
End If

rst.MoveNext

in effect, the loop appears to be locking the tables automatically. i set up some debug.prints to check to make sure i’m not just hitting the same table over and over again but that part seems fine. i think somehow the way i am opening the recordset might be the problem but nothing strikes me at this time. I’ve done this before (well not in a loop) and adding columns using SQL works fine.

that’s true but i’ve been using the same syntax as the second, working code. the only reason there’s the empty quotes is, i copy/pasted the VB code generated from a converted macro – which works in a new, empty DB but not on the one i am using. When i write the macro on the target DB, not only doesn’t it run but it won’t convert to VB.

In any case, I don’t think that is the issue – the same error occurs with or without the empty quotes.

yeah I thought of that yesterday – when i go to Add/Remove Programs and select Change from Office, there’s no option for ODBC drivers in the Access section.

It might, if I had the machine and could look for the files myself. but the user is not a computer person and is in another city. i don’t see asking her to do file searches and registry edits as a solution. i was hoping a SP might be in order, but her machine is up to date. Since other people can run the app without problems, it is a config issue. But I am not going to direct her to do this sort of sleuthing.

anyhow, thanks for helping!

Just to make sure we’re talking about the same thing, the issue i have is, if a directory exists but has no files, i get a file/path error on trying to place files in the directory. if the directory does not exist, the system creates it and then places files – no error. if the directory exists and contains some file (any file), i can write new files to it. I don’t understand why this happens, as a newly created directory is also empty. It’s a little weird. I don’t like fixing it by ignoring the error, but that does fix it, in the sense that it behaves as desired.

well, you still need the On Error Resume Next:

On Error Resume Next

If Dir(“C:RUGCalculator”) = “” Then
MkDir (“C:RUGCalculator”)
End If

no need to check if empty – the system will place files in an empty existing folder, overwrite files in an existing folder or re-create the folder if it doesn’t exist.

Thanks for the tip. i ended up doing this:

On Error Resume Next <– this forces the routine to complete; otherwise I get file/path error and execution halts.

If Dir("C:RUGCalculator") = "" Then
MkDir ("C:RUGCalculator")
Else
If Dir("C:RUGCalculator*.*") = "" Then
RmDir ("C:RUGCalculator")
MkDir ("C:RUGCalculator")
End If
End If

I just finished reading an article from MS that states:

Applications cannot, however, ignore a return of SQL_SUCCESS_WITH_INFO return code on the SQLConnect, SQLDriverConnect, or SQLBrowseConnect. This is because messages other than 5701 and 5703 that do require action may be returned.

in the case of a timeout, the SQL Server error code is 01000, which cannot be suppressed. I read this to mean “since you succeeded in connecting, we kindly provide you with the option to login”. which is what I don’t want, but looks like I’ll have to live with it.

Previously, I got clever and built in a ping component in VBA to test the connection and that worked – meaning I could message out before actually connecting, but unfortunately the network I am on has several nodes that don’t allow ping, so most people couldn’t use the system. Those that couldn’t ping can, however, connect.

Well, thanks for trying!

thanks – i tried to do on error goto errorHandler and in errorHander: i did this:

MsgBox “Error: (” & Err.Number & “) ” & Err.Description, vbCritical

this is what happens: the connect fails, and i get the SQL Server timeout reply and the login dialog box (neither of which i’d like to see). the error itself from Access is

Error: (3059) Operation canceled by user.

Now, what appears to be happening is the ODBC call goes “outside” of Access and returns the error notification and login screen. Which makes some sense, as the error is raised in the ODBC call, not Access.

BTW, this is the connectstring:

“ODBC;Driver=SQL SERVER;SERVER=;UID=NFUR_user;PWD=;Database=NFUR;”

No need to implement DSN files – this code does the connection. I was hoping there’d be an option to suppress the login bit but I haven’t found anything along those lines.

well, after spending a LOT of time on doing tabledefs, appends, etc etc I happened to notice Access has a provision for appending to — Another Database! — and it’s a one-liner:

DoCmd.RunSQL (“INSERT INTO

IN ‘\UNCDrivePathdbsskeltonXferDB.mdb’ SELECT * FROM ;”)

Sheesh!!!

There is the issue of duplicate submissions or people trying to do this while the targe db is locked, but still, there ya go.