By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

MS Access2000 frontend - MS SQL2000 Backend - dbSeeChanges

P: 14
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:

Expand|Select|Wrap|Line Numbers
  1. ConnectStr = "ODBC;dsn=rd_venteliste;UID=xxx;PWD=yyy;DATABASE=Venteliste"
  2. For Each t In CurrentDb.TableDefs
  3.     If t.SourceTableName <> "" Then
  4.         t.Connect = ConnectStr
  5.         t.RefreshLink
  6.     End If
  7. Next
  8.  
Filling a form works ok like:
Expand|Select|Wrap|Line Numbers
  1. dim rs as dao.recordset
  2. strSql = "SELECT * FROM Elev WHERE ElevID = " & OpenArgs
  3. Set rs = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot, dbSeeChanges)
  4.    Me.ElevID = rs.Fields!ElevID
  5.    Me.FamilyID = rs.Fields!FamilyID
  6.    .....
  7.    Me.AfgivendeSkole = rs.Fields!AfgivendeSkole
  8.    Me.Bemarkning = rs.Fields!Bemarkning
  9. rs.Close
  10.  
So do an insert:
Expand|Select|Wrap|Line Numbers
  1. CurrentDb.Execute ("INSERT INTO Family " & _
  2.                   " (Far, Mor) " & _
  3.                   " VALUES('" & Me.Far & _
  4.                   "', '" & Me.Mor & "')")
  5.  
But when it comes to an update things go wrong:
Expand|Select|Wrap|Line Numbers
  1. currentdb.update ("UPDATE Elev" & _
  2.                   " SET FamilyID = '" & Me.FamilyID & _
  3.                   "', ElevFornavn = '" & Me.ElevFornavn & _
  4.                   "', ElevEfternavn = '" & Me.ElevEfternavn & _
  5.           ...
  6.                   "', Bemarkning = '" & Me.Bemarkning & _
  7.                   "', Status = '" & Me.Status & _
  8.                   "' WHERE ElevID = '" & Me.ElevID & "'")
  9.  
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:
Expand|Select|Wrap|Line Numbers
  1. currentdb.update ("UPDATE Elev" & _
  2.                   " SET FamilyID = '" & Me.FamilyID & _
  3.                   "', ElevFornavn = '" & Me.ElevFornavn & _
  4.                   "', ElevEfternavn = '" & Me.ElevEfternavn & _
  5.           ...
  6.                   "', Bemarkning = '" & Me.Bemarkning & _
  7.                   "', Status = '" & Me.Status & _
  8.                   "' WHERE ElevID = '" & Me.ElevID & "'", dbSeeChanges)
  9.  
I get an error "Expected: =" ?

Can anyone explain - please ?

ravno
Apr 12 '08 #1
Share this Question
Share on Google+
15 Replies


Expert Mod 2.5K+
P: 2,545
Hi. Although it is an Update you want to run, the syntax CurrentDB.Update is not correct. Your Update should still be run using CurrentDB.Execute ([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
Apr 12 '08 #2

P: 14
Hi. Although it is an Update you want to run, the syntax CurrentDB.Update is not correct. Your Update should still be run using CurrentDB.Execute ([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.execute("Update").
ravno
Apr 12 '08 #3

Expert Mod 2.5K+
P: 2,545
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
Apr 13 '08 #4

P: 14
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
Apr 14 '08 #5

Expert Mod 2.5K+
P: 2,545
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. Begin trans
  3. ...
  4. CommitTrans
  5. 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
Apr 14 '08 #6

P: 14
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. Begin trans
  3. ...
  4. CommitTrans
  5. 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
Expand|Select|Wrap|Line Numbers
  1. 'ConnectStr = "ODBC;DSN=RideCenter;UID=RideCenter;PWD=***;DATABASE=xxx"
  2. ConnectStr = ";Database=" & FileName
  3.  
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
Apr 20 '08 #7

Expert Mod 2.5K+
P: 2,545
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
Apr 21 '08 #8

P: 14
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function RefreshBackEndLinks(FileName As String) As Boolean
  2.  
  3. Dim t As DAO.TableDef
  4. Dim ConnectStr As String
  5.  
  6. 'ConnectStr = "ODBC;DSN=RideCenter;UID=RideCenter;" & _ 
  7.                           "PWD=***;DATABASE=***"
  8. ConnectStr = ";Database=" & FileName
  9.  
  10. For Each t In CurrentDb.TableDefs
  11.     If t.SourceTableName <> "" Then
  12.         t.Connect = ConnectStr
  13.         t.RefreshLink
  14.     End If
  15. Next
  16. RefreshBackEndLinks = True
  17. End Function
  18.  
FileName is the full path - c:\folder\....\db.mdb' - to the Access-db. I check the t.SourceTableName 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:
Expand|Select|Wrap|Line Numbers
  1. ....
  2. Select BackEndType
  3.     Case MSSQL
  4.         ConnectStr  "ODBC;DSN=RideCenter;UID=RideCenter;" & _
  5.                            "PWD=***;DATABASE=***"
  6.     Case ACCESS
  7.         ConnectStr = ";Database=" & FileName
  8. End Select
  9. .....' Refreshlinks
  10.  
I have noticed, that t.SourceTableName='dbo.tablename' for the MS SQL database-link, while it is t.SourceTableName='tablename' in Access-link.

ravno
Apr 22 '08 #9

Expert 100+
P: 446
Hi , I'm just registering to see how this works out!

Ravno, is your front end in ANSI 92 mode ?

S7
Apr 22 '08 #10

P: 59
running odbc db's is a massive headache... :D ( | ) < big pill
Apr 22 '08 #11

P: 14
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
Apr 23 '08 #12

P: 14
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
Apr 23 '08 #13

Expert 100+
P: 446
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
Apr 23 '08 #14

P: 14
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
May 5 '08 #15

P: 14
This thread can be closed

ravno
Feb 3 '09 #16

Post your reply

Sign in to post your reply or Sign up for a free account.