MS Access2000 frontend - MS SQL2000 Backend - dbSeeChanges

[ravno]

I am developing an application in MS Access2000 - back-end database MS SQL 2000.
When the application starts up, it refreshes link to backend like:

ConnectStr = "ODBC;dsn=rd_venteliste;UID=xxx;PWD=yyy;DATABASE=Venteliste"
For Each t In CurrentDb.TableDefs
    If t.SourceTableName <> "" Then
        t.Connect = ConnectStr
        t.RefreshLink
    End If
Next
 

Filling a form works ok like:

dim rs as dao.recordset
strSql = "SELECT * FROM Elev WHERE ElevID = " & OpenArgs
Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot, dbSeeChanges)
   Me.ElevID = rs.Fields!ElevID
   Me.FamilyID = rs.Fields!FamilyID
   .....
   Me.AfgivendeSkole = rs.Fields!AfgivendeSkole
   Me.Bemarkning = rs.Fields!Bemarkning
rs.Close
 

So do an insert:

CurrentDb.Execute ("INSERT INTO Family " & _
                  " (Far, Mor) " & _
                  " VALUES('" & Me.Far & _
                  "', '" & Me.Mor & "')")
 

But when it comes to an update things go wrong:

currentdb.update ("UPDATE Elev" & _
                  " SET FamilyID = '" & Me.FamilyID & _
                  "', ElevFornavn = '" & Me.ElevFornavn & _
                  "', ElevEfternavn = '" & Me.ElevEfternavn & _
          ...
                  "', Bemarkning = '" & Me.Bemarkning & _
                  "', Status = '" & Me.Status & _
                  "' WHERE ElevID = '" & Me.ElevID & "'")
 

I get error (my best translation from Danish):
"You must use option dbSeeChanges with OpenRecordSet at access to a SQL-Server-table, which has an Identity-Column"

When I try something like this:

currentdb.update ("UPDATE Elev" & _
                  " SET FamilyID = '" & Me.FamilyID & _
                  "', ElevFornavn = '" & Me.ElevFornavn & _
                  "', ElevEfternavn = '" & Me.ElevEfternavn & _
          ...
                  "', Bemarkning = '" & Me.Bemarkning & _
                  "', Status = '" & Me.Status & _
                  "' WHERE ElevID = '" & Me.ElevID & "'", dbSeeChanges)
 

I get an error "Expected: =" ?

Can anyone explain - please ?

ravno

[Stewart Ross]

Hi. Although it is an Update you want to run, the syntax CurrentDB.Updat e is not correct. Your Update should still be run using CurrentDB.Execu te ([update statement]) just as you did for the INSERT statement.

Execute is used for all types of action queries (insert, delete, update).

What is puzzling is that there is no Update method of the Database object (CurrentDB returns a pointer to the current database object that is in use). I can't explain why you are getting the error messages you quote, as I would have expected a syntax error of some kind, but I am certain that you should be using the Execute method and not the word Update in the statement you quote.

-Stewart

[ravno]

Hi. Although it is an Update you want to run, the syntax CurrentDB.Updat e is not correct. Your Update should still be run using CurrentDB.Execu te ([update statement]) just as you did for the INSERT statement.

Execute is used for all types of action queries (insert, delete, update).

What is puzzling is that there is no Update method of the Database object (CurrentDB returns a pointer to the current database object that is in use). I can't explain why you are getting the error messages you quote, as I would have expected a syntax error of some kind, but I am certain that you should be using the Execute method and not the word Update in the statement you quote.

-Stewart

Thank you - I am sorry to see, I've 'quoted' my code wrong - I use the currentdb.execu te("Update").
ravno

[Stewart Ross]

Hi again. Why not try the DoCmd.RunSQL statement instead? It would be used like this:

DoCmd.RunSQL "Update ... "

With regard to your Execute statement DbSeeChanges is a constant, and the error message makes no sense. Using the DbSeeChanges option is intended to generate a run-time error if another user is changing the data at the same time as the action query is executed - not recommended really.

I have attached the following extract from the Access help on the Execute command which I do think is more pertinent, and I would suggest enclosing the execute (without DBSeeChanges) in the transaction statements as mentioned:

For best performance in a Microsoft Jet workspace, especially in a multiuser environment, nest the Execute method inside a transaction. Use the BeginTrans method on the current Workspace object, then use the Execute method, and complete the transaction by using the CommitTrans method on the Workspace. This saves changes on disk and frees any locks placed while the query is running.

-Stewart

[ravno]

Hi again. Why not try the DoCmd.RunSQL statement instead? It would be used like this:

DoCmd.RunSQL "Update ... "

With regard to your Execute statement DbSeeChanges is a constant, and the error message makes no sense. Using the DbSeeChanges option is intended to generate a run-time error if another user is changing the data at the same time as the action query is executed - not recommended really.

I have attached the following extract from the Access help on the Execute command which I do think is more pertinent, and I would suggest enclosing the execute (without DBSeeChanges) in the transaction statements as mentioned:



-Stewart

Hi Stewart

Looks like the docmd.runsql () works all right - I use it together with the begintrans and the committrans
Would it be an idea to run my inserts/deletes in the same way ?
And finally - how do I avoid to have to confirm all updates - ms acces asks something like: "you're about to update 1 row - you won't be able to undo - do you wan't to update? "

ravno

[Stewart Ross]

Hi Ravno. Glad this worked out for you. You may not need the begin trans and committrans methods, but if these are working for you leave them in place. Your deletes and inserts will work fine with RunSQL, but as they are working with Execute I would leave them in place as is.

There is more than one way to turn off the warnings. One I use is to set the warnings off for the update and set them back on afterwards.

It is used like this:

DoCmd.SetWarnings False
Begin trans
...
CommitTrans
DoCmd.SetWarnings True

-Stewart

Hi Stewart

Looks like the docmd.runsql () works all right - I use it together with the begintrans and the committrans
Would it be an idea to run my inserts/deletes in the same way ?
And finally - how do I avoid to have to confirm all updates - ms acces asks something like: "you're about to update 1 row - you won't be able to undo - do you wan't to update? "

ravno

[ravno]

Hi Ravno. Glad this worked out for you. You may not need the begin trans and committrans methods, but if these are working for you leave them in place. Your deletes and inserts will work fine with RunSQL, but as they are working with Execute I would leave them in place as is.

There is more than one way to turn off the warnings. One I use is to set the warnings off for the update and set them back on afterwards.

It is used like this:

DoCmd.SetWarnings False
Begin trans
...
CommitTrans
DoCmd.SetWarnings True

-Stewart

Hi Stewart

It now looks like what I wanted - thank you very much.

One final question - I would like to be able to link to the original msAccess-database - using this

'ConnectStr = "ODBC;DSN=RideCenter;UID=RideCenter;PWD=***;DATABASE=xxx"
ConnectStr = ";Database=" & FileName
 

where I use the first to connect to mssql. When I try the second and have pointed the database out in FileName - the system wants a Data Source and there it all stops
Is it maybe a new thread ?

ravno

[Stewart Ross]

Hi Ravno. We'd need to see the function or procedure that is doing the linking - not just the connect string.

One thing I don't yet understand from what you have mentioned is that if the Access tables you need to use are present in the database in which your code is running, they are already connected (at start-up of the database) so why do you need to connect to them again?

If you are working in code within Access you can use DAO (or ADO) recordset functionality to open the available recordsets (tables and queries) directly, unless that is you are establishing connections to another Access DB or to non-Access tables (as you did for your MYSQL tables). Perhaps a new thread is indeed needed here, but feel free to answer in this one until we see where it takes us...

-Stewart

[ravno]

Hi Ravno. We'd need to see the function or procedure that is doing the linking - not just the connect string.

One thing I don't yet understand from what you have mentioned is that if the Access tables you need to use are present in the database in which your code is running, they are already connected (at start-up of the database) so why do you need to connect to them again?

If you are working in code within Access you can use DAO (or ADO) recordset functionality to open the available recordsets (tables and queries) directly, unless that is you are establishing connections to another Access DB or to non-Access tables (as you did for your MYSQL tables). Perhaps a new thread is indeed needed here, but feel free to answer in this one until we see where it takes us...

-Stewart

Hi Stewart
In my front-end, the users are able to select the backend-db, using an 'OpenFileDialog ' and then the function below. It works fine, when the backend is an Access-db. I would like to set up, if it should link to an Access- or to a MS SQL-backend - the user should be able to switch - otherwise I would have to maintain 2 front-ends.
When I open the front-end, the linked-table-manager tells me, the front-end is linked to the MS-SQL-database. When I try to update all tables - 'allways prompt for new location', it asks for a datasource and I'm not able to point out my Access-db.
The following code is called, when the front-end runs:

Public Function RefreshBackEndLinks(FileName As String) As Boolean
 
Dim t As DAO.TableDef
Dim ConnectStr As String
 
'ConnectStr = "ODBC;DSN=RideCenter;UID=RideCenter;" & _ 
                          "PWD=***;DATABASE=***"
ConnectStr = ";Database=" & FileName
 
For Each t In CurrentDb.TableDefs
    If t.SourceTableName <> "" Then
        t.Connect = ConnectStr
        t.RefreshLink
    End If
Next
RefreshBackEndLinks = True
End Function
 

FileName is the full path - c:\folder\....\ db.mdb' - to the Access-db. I check the t.SourceTableNa me as there are some non-linked tmp-tables in the front-end, which are not to be re-linked.

The idea is to end up with something like this:

....
Select BackEndType
    Case MSSQL
        ConnectStr  "ODBC;DSN=RideCenter;UID=RideCenter;" & _
                           "PWD=***;DATABASE=***"
    Case ACCESS
        ConnectStr = ";Database=" & FileName
End Select
.....' Refreshlinks
 

I have noticed, that t.SourceTableNa me='dbo.tablena me' for the MS SQL database-link, while it is t.SourceTableNa me='tablename' in Access-link.

ravno

[sierra7]

Hi , I'm just registering to see how this works out!

Ravno, is your front end in ANSI 92 mode ?

S7