|
 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
|
 |
|
|
 |

September 6th, 2003, 05:12 PM
|
Registered User
|
|
Join Date: Sep 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Queries
Could anyone help me? i have a laptop and desktop which runs on xp pro. I set up a program which inserts into database and realised after running it on my desktop, it works fine, but when it comes to my desktop, there is an error saying "query must be an updateable query". could anyone help? i help one could actually give rights but i do not know how to set the permissions. i tried many many ways to access permissions? could anyone tell me the correct procedures to setting *.* mdb extensions?
|

September 6th, 2003, 10:07 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
Quote:
quote:...but when it comes to my desktop...
|
You mean, "when it comes to my laptop"?
It doesn’t sound like permissions on the .mdb file are your problem. If you haven't manually implemented user-level security on your db through either the Security Wizard or code you are, by default, logged into you .mdb as a user named Admin with full administrative permissions. Access doesn’t use Windows Integrated Security; you have to manually create your own workgroup information file to restrict permissions on individual objects in the database (usually by running the Security Wizard). The only way you could run into permission related write problems without having implemented user-level security yourself is if you opened the database as read-only, or if the db is located on a read-only drive (I’m assuming you have your file installed locally).
That said there are many reasons why an individual query might not be updateable. Can you edit the base tables on which the query is based? If so, and the query joins more than one table, check in query design view and make sure the proper join lines are displayed. If you can edit the data in the base tables, something is wrong with your query definition. Are you using DAO/ADO recordsets or linked tables by any chance? ‘Bout all I can think of at the moment since it seems to run OK on the other machine, if I understand you correctly, an you didn't mention a network connection. When all else fails, re-create the query definition and compact and repair.
Bob
|

September 8th, 2003, 01:47 AM
|
Registered User
|
|
Join Date: Sep 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
 I would like to verify that my SQL insert statement works on my desktop and not my laptop. I'm using ADO recordset for my connection. i have yet to solve this problems and it seems impending. Is there a problem with my SQL statement?
Another problem that arises: I have insert into database with something like this '"+something.Text+"' but i realised that when i try to insert a string (i'm going to my aunt's breakfast), i can't insert. A error showing "statments having quotes can't be insert". if i am not wrong, the quotes have an effect on the input string. So is there an way to solve this problem?
|

September 8th, 2003, 08:58 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
As far as your ADO recordset is concerned, be sure that your connection string knows how to locate your .mdb file, and that your cursor type and lock type properties are set to return an updateable cursor (the default cursor type is forward-only, and the default lock-type is read-only). Try something like this:
Private Sub Command0_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM tblCustomers"
' Open an updateable recordset
rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
' Insert a new record
rst.AddNew
With rst
!CustomerID = 1
!FirstName = "John"
!LastName = "Doe"
' Save changes to recordset
.Update
End With
rst.Close
End Sub
Using adOpenDynamic with adLockOptimistic actually return an updateable keyset cursor (not a dynamic cursor). Jet doesn't support dynamic cursors. This just means that you can update the recordset, but you can't see changes made by other users in a multi-user environment until you refresh the cursor (e.g., reopening the recordset).
If you are using a literal character string for you connection string, make sure the path is correct:
Private Sub Command0_Click()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strProvider As String
Dim strDataSource As String
Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
strProvider = "Microsoft.Jet.OLEDB.4.0"
strDataSource = CurrentProject.Path & "\MyDatabase.mdb"
cnn.Open "Provider = " & strProvider & "; Data Source = " & strDataSource
strSQL = "SELECT * FROM tblCustomers"
For your SQL criteria string, notice where the additional apostrophe's are placed in the following:
strCriteria = "[LastName] = '" & txtLastName & "'"
The original criteria expressioin:
"[LastName] = '" & txtLastName & "'"
evaluates as
"[LastName] = '" & "Doe" & "'"
which, when concatenated, evaluates as:
"[LastName] = 'Doe'"
If your criteria is numeric, use:
strCriteria = "[CustomerID] = " & txtCustomerID
The original criteria expression
"[CustomerID] = " & txtCustomerID
evaluates as
"[CustomerID] = " & 1
which, when concatenated, evaluates as:
"[CustomerID] = 1"
For date fields, use:
strCriteria = "[OrderDate] = #" & txtDate & "#"
HTH,
Bob
|

September 8th, 2003, 09:27 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
Lastly, if you are using a SQL INSERT statement to add a record instead of the recordset's AddNew method, you don't need a recordset object at all. You only need a connection on which to execute the SQL command:
Private Sub Command0_Click()
Dim cnn As ADODB.Connection
Dim strProvider As String
Dim strDataSource As String
Set cnn = New ADODB.Connection
strProvider = "Microsoft.Jet.OLEDB.4.0"
strDataSource = CurrentProject.Path & "\MyDatabase.mdb"
cnn.Open "Provider = " & strProvider & "; Data Source = " & strDataSource
cnn.Execute "INSERT INTO tblCustomers SELECT * FROM tblCustomers2 " & _
"WHERE [LastName] = '" & txtLastName & "'"
End Sub
|

September 8th, 2003, 10:03 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
|
|
Quote:
quote:Is there a problem with my SQL statement?
|
Post it :)
|
Thread Tools |
Search this Thread |
|
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
 |