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?

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

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?

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

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

Post it :)