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

[blad3runn69]

running odbc db's is a massive headache... :D ( | ) < big pill

[ravno]

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

Ravno, is your front end in ANSI 92 mode ?

S7

Hi Sierra7

Now you're getting technical - I do not know, what to answer - could you tell more ?

ravno

[ravno]

running odbc db's is a massive headache... :D ( | ) < big pill

Hi blad3runn69

Maybe - but have you got a better idea, when I want to connect to the MS Sql-back-end ?

ravno

[sierra7]

Hi Sierra7

Now you're getting technical - I do not know, what to answer - could you tell more ?

ravno

Hi Ravno
I have only just encountered this myself so am not too sure of my facts. The 'auto-complete' stopped working in some combo boxes and I tracked this down to that in Access 2002 there is an option (in Tools > Options > Tables/Queries) to set 'SQL Server Compatibility Syntax ANSI 92'. The purpose is to make Access develeopments more easily upgradable to SQL Server, and this had been checked.

"What is the difference?" I hear you ask ! Well the first thing that impacts the code is that the 'Select ' statements in a combo boxes must become 'SELECT DISTINCT'. After changing they still work in ANSI 89 so you don't need two versions (yet).

The second is the wildcard characters have changed from "*' and '?' to '%' and '_'. I don't use wildcards much but will have to review all my queries. Users will have to be re-educated but I guess this is common for SQL Server backends.

I was getting some other errors before finding the app was in ANSI 92 mode, which was when I changed back, but I have been researching what else might be affected.

I have read that 'not equals' may have changed from '<>' to '!=' but I need to check if that article was refereing to MySQL not SQL Server.

Similarly, I have come across another article saying that the JOIN syntax has changed but as far as I can see Access has always explicitly stated the type of join in the FROM clause not the WHERE clause, so I won't waste space here, but Microsoft imply there are other difference but I have not tracked them down yet.

There is another issue of using DISTINCT in an aggregate function, for which I again refer to Microsoft for the resolution.

I like the idea of maintaining one front-end that can be used against either an Access or SQL backend but doubt that is possible unless complying with ANSI 92. I don't know whether all this applies to SQLServer 2000 but it might be worth taking Access to 2002 if it does.

I hope this has helped

S7

[ravno]

Hi Ravno
I have only just encountered this myself so am not too sure of my facts. The 'auto-complete' stopped working in some combo boxes and I tracked this down to that in Access 2002 there is an option (in Tools > Options > Tables/Queries) to set 'SQL Server Compatibility Syntax ANSI 92'. The purpose is to make Access develeopments more easily upgradable to SQL Server, and this had been checked.

"What is the difference?" I hear you ask ! Well the first thing that impacts the code is that the 'Select ' statements in a combo boxes must become 'SELECT DISTINCT'. After changing they still work in ANSI 89 so you don't need two versions (yet).

The second is the wildcard characters have changed from "*' and '?' to '%' and '_'. I don't use wildcards much but will have to review all my queries. Users will have to be re-educated but I guess this is common for SQL Server backends.

I was getting some other errors before finding the app was in ANSI 92 mode, which was when I changed back, but I have been researching what else might be affected.

I have read that 'not equals' may have changed from '<>' to '!=' but I need to check if that article was refereing to MySQL not SQL Server.

Similarly, I have come across another article saying that the JOIN syntax has changed but as far as I can see Access has always explicitly stated the type of join in the FROM clause not the WHERE clause, so I won't waste space here, but Microsoft imply there are other difference but I have not tracked them down yet.

There is another issue of using DISTINCT in an aggregate function, for which I again refer to Microsoft for the resolution.

I like the idea of maintaining one front-end that can be used against either an Access or SQL backend but doubt that is possible unless complying with ANSI 92. I don't know whether all this applies to SQLServer 2000 but it might be worth taking Access to 2002 if it does.

I hope this has helped

S7

Hi Sierra7

Sorry bout the long time - I had to do som Access-programming ....

About connecting to back-ends: When I tried to connect to Access Back-end after having connected to SQL - se previous - it looked as the connection to my sql-server got broken - I couldn't connect - not even using the Enterprise Manager. I had to delete my server-registration and make a new one using the IP-address - before I used a server name.

So all in all I put the 'one -frontend-project' to sleep for a while.

ravno