Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 6th, 2003, 05:12 PM
Registered User
 
Join Date: Sep 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?



Reply With Quote
  #2 (permalink)  
Old 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
Default

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


Reply With Quote
  #3 (permalink)  
Old September 8th, 2003, 01:47 AM
Registered User
 
Join Date: Sep 2003
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

Reply With Quote
  #4 (permalink)  
Old 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
Default

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

Reply With Quote
  #5 (permalink)  
Old 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
Default

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

Reply With Quote
  #6 (permalink)  
Old 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
Default

Quote:
quote:Is there a problem with my SQL statement?
Post it :)



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
requst for help queries mohan.lalapeta MySQL 0 March 5th, 2007 02:35 AM
How can I Speed Up My Queries Corey Access 1 February 1st, 2007 08:38 AM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM
Update queries socoolbrewster Access 4 March 4th, 2004 06:18 PM
Automate Queries Ben Access VBA 3 January 29th, 2004 08:50 AM



All times are GMT -4. The time now is 10:01 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.