Hi:
I am experencing very strange case.
I have two identical MS access application, only difference is one backend is MS database, another is MS Sql database.
When I run the application with the sql backend, one form just doesn't allow me to edit, even I can select value from a comboBox control.
(But the application with the MS database backend works fine).
I don't know what will casuse the problem, do I need to do something with the sql data table permmission or what?
Thank you very much.
Jt
6 2080
Hi:
I am experencing very strange case.
I have two identical MS access application, only difference is one backend is MS database, another is MS Sql database.
When I run the application with the sql backend, one form just doesn't allow me to edit, even I can select value from a comboBox control.
(But the application with the MS database backend works fine).
I don't know what will casuse the problem, do I need to do something with the sql data table permmission or what?
Thank you very much.
Jt
A quickie for you to look at. Do you have your 'primary' key set in the MS SQL database table that the form services?
Jim
Hi: Jim
Thank you very much for your reply.
I checked the table in SQL, it has primary key.
I am using this connection:
strSQL = "SELECT actDecsription FROM codAction WHERE actActionID = 3"
rst.Open strSQL, strCnxn, adOpenKeyset, adLockOptimistic
Set Me.Recordset = rst ' trying to recordset to bind the form
On the form, only has a comboBox (name is:"cobBoxDescr"), what I find is:
1. if I add "actDescription" to "Control Source" of the comboBox, the the form becomes read only. (control bound)
But if I
1. leave the "Control Source" of the comboBox empty. (unbound).
2. remove the line: Set Me.Recordset = rst
3. add new line: Me.cobBoxDescr.Value = rst.Fields("actDecsription").Value
Then the form becomes editable.
At least I can make the change (or select other List values from the comboBox dropdown).
Thank you very much.
Jt
Hi: Jim
I am trying to do a simple test here: create another combo box, put the combo box Control Source = codAction!actDecsription (using expression builder), when I open the form, the combo box has values, but if i select one of them. on the bottom of the statuse bar says: "Control can't be edited, it's bound to expression "codAction!actDecsription ".
I don't know why that happens.
Thanks.
jt
Hi: Jim
I am trying to do a simple test here: create another combo box, put the combo box Control Source = codAction!actDecsription (using expression builder), when I open the form, the combo box has values, but if i select one of them. on the bottom of the statuse bar says: "Control can't be edited, it's bound to expression "codAction!actDecsription ".
I don't know why that happens.
Thanks.
jt
James I'm not quite sure what you re doing here I can see 'now' that you are returning recordset objects and then setting the recordset of the form to the return dataset provided by that SQL. The dropdown I assume you have a different dataset for the rowsource of that?
Anyway in relation to your forms recordset I do not know what method you are using for connection to SQL server but have a look at the MS DataShape method of connection which will allow for your 'editing' needs once the recordset is bound to the form. You must have a reference set to the Microsoft ActiveX Data Objects 2.1 Library
Use this in the on open event of your form and amend the connection string to suit your sql server name relevant user name and password
I suggest to study this method and in particular look at the intentions behind declaring cursors client side and server side. I could go into it for you but could also write a book on it. Have also a look at the HOWTO's on this site where detailed explanations have already been kidly done by other experts on here. In addition I will give you a link to the MS site that explains recordset behaviour in an ODBC context and also the MSDataShape useage to make your recordset editable. http://support.microsoft.com/kb/227053/EN-US/ - Dim cn As ADODB.Connection
-
Dim rs As ADODB.Recordset
-
Set cn = New ADODB.Connection
-
With cn
-
.Provider = "MSDataShape"
-
'The code below uses a sample server name, user ID, and password.
-
'Be sure to use your actual server name, user ID, and password.
-
.ConnectionString = "DATA PROVIDER=SQLOLEDB;DATASOURCE=yoursqlservername;DATABASE=yourdatabasename;UID=username;PWD=theuserspassword"
-
.CursorLocation = adUseServer
-
.Open
-
End With
-
-
Set rs = New ADODB.Recordset
-
With rs
-
.Source = "SELECT actDescription FROM codAction WHERE actActionID =3"
-
.ActiveConnection = cn
-
.CursorType = adOpenKeyset
-
.LockType = adLockOptimistic
-
.Open
-
End With
-
Set Me.Recordset = rs
-
Me.UniqueTable = "codAction"
In addition to this as a point of principle of 'differences in flavour' as to methods of connection and as additional reference material for you to study you also might wish to look at the HOWTO article created by ADezii concerning creation of UDL files for instance to establish SQL server connections in this thread http://www.thescripts.com/forum/thread744230.html
Regards
Jim :)
Hi: Jim
Thank you very much for your help.
After another hrs of trying, final I found problem, you are right at the beginning, I didn't set the primary key for the sql table, but I added a primary follow your suggestion immediatly.
But still not working, why? because I should go to the linked table manager and refresh the link.(I didn't realize I should do that).
Everything backs to normal, now the data in the form are editable.
Thanks Jim
jt
Hi: Jim
Thank you very much for your help.
After another hrs of trying, final I found problem, you are right at the beginning, I didn't set the primary key for the sql table, but I added a primary follow your suggestion immediatly.
But still not working, why? because I should go to the linked table manager and refresh the link.(I didn't realize I should do that).
Everything backs to normal, now the data in the form are editable.
Thanks Jim
jt
Hi James I,m glad you got it working I wish I'd placed a 6 to 4 bet on the first answer I instinctively gave now haha.
Regards
Jim :)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: James Bird |
last post by:
Hello
In order to perform a hetrogeneous join (I think that's what they're called)
between MySQL and another database, I've created an Access database
containing linked tables from each...
|
by: Matthew Wells |
last post by:
Good morning...
I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added...
|
by: intl04 |
last post by:
How do I create a data input form in Access that is external to the
Access database to which it's connected (if that's possible, which I
believe it is)? For example, if someone clicks on an Access...
|
by: James Bird |
last post by:
Hello
In order to perform a hetrogeneous join (I think that's what they're called)
between MySQL and another database, I've created an Access database
containing linked tables from each...
|
by: Kenneth Courville |
last post by:
Hello, I'm looking for assistance with the Access object model. I know
this is VB, but I'm building an Office Add-using C# directed at Access
2002. I'm literate in VB, so you can reply in VB... I...
|
by: Neil Ginsberg |
last post by:
I have a client who is using Access 2002/2000 (the database itself is
written in 2000), and is considering migrating to Access 2003. Any
recommendations on whether Access 2003 is worth the migrate,...
|
by: brettclare |
last post by:
I have linked a large SQL Server table to Access, however 'only'
2,195,439 records are shown and are available to query. Can I increase
the size (cache??)/number of records showing in Access?
...
|
by: James Bird |
last post by:
Hello
In order to perform a hetrogeneous join (I think that's what they're called)
between MySQL and another database, I've created an Access database
containing linked tables from each...
|
by: k2storm |
last post by:
I'm having problems with Reports and I'm seeking some help, or advice on how to solve this problem. Any help will be much appriciated!
It’s hard to explain but I will try to do my best:
In my...
|
by: Sid |
last post by:
I hope someone could help me with this.
I am trying to setup a criteria to decide when to allow/not allow
user
to click on the check box. logically it looks simple but I am not
able
to...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |