Access FE to SQL Server BE

[Pat]

What do i need to look out for if i use an existing Access FE that was linked to an Access BE but now is to be linked to a SQL Server BE.
Are there any VBA code considerations involved?
What needs to be changed in the Access FE?

[HansV]

Make sure that all tables in the SQL Server database have a unique index.

The SQL dialect used in SQL Server uses _ and % as wildcards instead of ? and *, so you may have to update your queries and SQL statements.

[aardvark]

What I have found to be a bugaboo is that in my organization we have to have SQLServer dba's to monitor the backend. What this means to me is that everytime a structure change needs to be made in the tables, I have to solicit the services of the dba; not always the best use of time.

Also, be sure to set up an ODBC connection so that your front end knows where the back end is.

[Pat]

Hans,
The wildcards only need to be like that if i use passthru queries or if in an ADP access FE, isn't that right?

aard,
I already have the DSNs setup as it has to select one of about 8 different SQL Server databases.

[HansV]

You should use passthrough queries whenever possible - they are much more efficient than local queries.

[Pat]

I have noted the pass thru queries.
Is there any doco on this i can read?

[HansV]

See for example this tutorial.

[Wendell]

With regard to passthrough queries, we do use them, but only when the performance of an Access query isn't acceptable. For most simple Access queries (two or less joins), the ODBC driver will convert the Access SQL string to SQL Server dialect and actually run the query in SQL Server. On average we find about 90 percent of the queries run just fine. Another trick we use is to create views in SQL Server (like a query in Access), and then link to the view so that Access sees it as a table. In some cases you may need to create an Indexed View if the dataset is large, but in most cases that isn't necessary.

[Mark L]

What do i need to look out for if i use an existing Access FE that was linked to an Access BE but now is to be linked to a SQL Server BE.
Are there any VBA code considerations involved?
What needs to be changed in the Access FE?

There is one potentially major difference when using MDB vs SQL backend. Access fills an autonumber field as soon as you make a record dirty. SQL only does it when it writes new record.

[Pat]

What do i need to look out for if i use an existing Access FE that was linked to an Access BE but now is to be linked to a SQL Server BE.
Are there any VBA code considerations involved?
What needs to be changed in the Access FE?

There is one potentially major difference when using MDB vs SQL backend. Access fills an autonumber field as soon as you make a record dirty. SQL only does it when it writes new record.

How do you get the autonumber field from SQL Server after it is written, i have noticed in the code it uses this. Is there a certain technique to get that number?

[HansV]

Let's say that the AutoNumber field is named ID and that you have opened a DAO recordset named rst on the table.

With a local Access table, you can use code like this:

Dim lngID As Long
' Create a new record
rst.AddNew
' Get the value of the AutoNumber field of the new record
lngID = rst!ID
rst!OtherField = "Something"
' Save the record
rst.Update

For a linked SQL Server table, lngID would not be assigned the value of the AutoNumber field in the new record, but the value of the record that was active before AddNew, because SQL Server will only determine this value when the record is saved.

Dim lngID As Long
' Create a new record
rst.AddNew
rst!OtherField = "Something"
' Save the new record
rst.Update
' Move to the record just created
rst.Bookmark = rst.LastModified
' Retrieve the value of the AutoNumber field
lngID = rst!ID

[Pat]

That is exactly what i am looking for, thank you.

In one of the tables there is a field that is a copy of the autonumber field (not my design, i have inherited this database) in a field called BookingNo, does the rst.Bookmark = rst.LastModified command enable the edit of that last record or do i have to use rst.Edit after that bookmark command?

[HansV]

In DAO, you have to use

rst.Edit

before you start editing the record; rst.Update takes the record out of edit mode.

[Pat]

Thanks Hans, i thought as much.
so is the following correct?

Dim lngID As Long
' Create a new record
rst.AddNew
rst!OtherField = "Something"
' Save the new record
rst.Update
' Move to the record just created
rst.Bookmark = rst.LastModified

' put the record into Edit mode
rst.Edit

' Update the BookingNo field with the AutoNumber field
rst!BookingNo = rst!ID
' Retrieve the value of the AutoNumber field
lngID = rst!ID
' Save the record back to the database
rst.Update

[HansV]

That looks OK; if you don't need the value of the ID for other purposes, you can omit the line

lngID = rst!ID

(and the declaration of lngID); you already assign the value of the ID field directly to BookingNo.

[Pat]

Thanks Hans

[Pat]

Let's say that the AutoNumber field is named ID and that you have opened a DAO recordset named rst on the table.

With a local Access table, you can use code like this:

Dim lngID As Long
' Create a new record
rst.AddNew
' Get the value of the AutoNumber field of the new record
lngID = rst!ID
rst!OtherField = "Something"
' Save the record
rst.Update

For a linked SQL Server table, lngID would not be assigned the value of the AutoNumber field in the new record, but the value of the record that was active before AddNew, because SQL Server will only determine this value when the record is saved.

Dim lngID As Long
' Create a new record
rst.AddNew
rst!OtherField = "Something"
' Save the new record
rst.Update
' Move to the record just created
rst.Bookmark = rst.LastModified
' Retrieve the value of the AutoNumber field
lngID = rst!ID

Will the Sql Server code above work for Access as well? I would presume so, am i correct?

[HansV]

The code should work OK in Access, but as mentioned earlier in this thread, it could be done in a slightly easier way if the data are stored in an Access database since the new AutoNumber value is available as soon as you modify any field in the new record, you don't have to save it first.

[Pat]

The reason i ask this is that i converted all their databases for this app to SQL Server and accrdingly changed the code to suit SQL Server. Now i find out that they require one of the databases left in Access, hence the question.
Since they may change their mind in the future i thought if the code works inAccess as well then i dont need to keep changing this code.

Thanks Hans, Welcome back, hope you had a good break.

[HansV]

If you want to be able to use the same code for Access and SQL backends, it is indeed best to use the code that works in SQL Server.

And thanks, I had a wonderful holiday in Italy.