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

[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

[ravno]

This thread can be closed

ravno