Access to SQL tables (AccessXP SP3)

Topic

New Reply

I am using an Access database that I upsized the tables into SQL Server 2005. I have a function to write to an Audit table that works fine with Access tables, but does not work with the SQL tables. The code I used is below. I added the db SeeChanges option to the Open Recordset method as I was prompted to do so by the system. This eliminated the error message I was getting, but the records do not write to the table.

Sub WriteAuditUpdateToTemp(txtTableName, lngRecordNum, txtFieldName, OrgValue, CurValue)
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(“Select * from rAuditTemp”, , dbSeeChanges)
rs.AddNew
rs!TableName = txtTableName
rs!RecordPrimaryKey = lngRecordNum
rs!FieldName = txtFieldName
rs!LoginName = GetCurrentUserName
rs!MachineName = GetComputerName
rs!User = CurrentUser ‘returns Access Security name
rs!OriginalValue = OrgValue
rs!NewValue = CurValue
rs!DateTimeStamp = Now()
rs.Update
rs.Close
db.Close
End Sub

Any ideas on why this will not work with SQL tables?

Carla

Reply | Quote

Replies

Does it help if you use

Set rs = db.OpenRecordset(“rAuditTemp”, dbOpenDynaset, dbSeeChanges)

Reply | Quote

Hans,

Works perfect. Thank you, Thank you, Thank you — spent a lot of the day tryiing to get this to work yesterday.

Carla

Reply | Quote

When working with linked tables, it’s always best to use dbOpenDynaset, otherwise the recordset will not be updatable. And since it doesn’t harm with non-linked tables,, I made it a habit to always use dbOpenDynaset.

Reply | Quote

This is a function that SQL Server triggers do very nicely – and it doesn’t matter where you do the edits in Access – even edits at the table level get captured. We routinely archive the old record that way in some applications so it can be compared with the current or prior versions of the same record. If you are interested, I can conjur up a simple example, although the real world seldom works that way.

Reply | Quote

Wendell,

Thank you for the idea. I am just looking for a temporary solution until we get a product called Log Explorer that will capture Edits, Updates, and Deletions and allow us to rollback to any point. Hans got us going for now.

I would be interested in an example if you have time. I have clearly found out real world is to keep working on what you need until you find a solution. I am just starting to work with SQL back end, Access front end combinations. The real catch is we are on a Novell network. I was out yesterday at a field office about 100 miles away from the main office and did some testing for the first system we are planning to implement first of the year and the performance is outstanding compared to the Access front and backend combinations in the same locations.

Carla

Reply | Quote

The beauty of SQL Server backends is that you can have them deliver just the info you need, while Access always pulls the tables into memory on the workstation if Access tables are used. On a slow LAN that can really be painful. I’ll construct a simple example of archiving using SQL Server, but it will likely be the weekend before I get to it.

Reply | Quote

Carla,

I am just looking into using SQL Server as the backend with access as the front end as you implied that you are doing. Have you made any progress? Any suggestions? Was it easy to move access tables to Sql server and execute code? Just curious.

Reply | Quote

Gary,

We have made pretty successful progress. First obstacle was to remove the databases from the workgroup security scenario. The Access forum provided the solution.The first database we have worked with we used the upsize wizard in Access to move the tables to Sql Server. After upsizing the tables we went through each table and checked the datatypes of all the fields and changed many of them, the upsize wizard did not always make good choices. Autonumber fields all needed to be redefined the wizard did not transition these. Relationships had to be recreated and decisions made for cascading deletions, and updates. If you use the upsize wizard you also have to rename all all of your tables. It appends the owner to the table name example tblVessels will become dbo_tblVessels. This will muck up any code that refrences tables. If using the wizard the connection to the SQL Server is established. Linked table manager will work with an ODBC connection for relinking. We did not lose and existing data or experience any data corruption.

The only code problem we encountered was the one in this post. Hans suggestion resolved the problem entirely.

We have decided to only use .mdb files for front end development and not use the .adp files. There are some posts in the forum about the differences.

The important things so far have been to PLAN how the server is to be setup organizationally. How is the server to be partitioned. Where are the databases going to be stored, where are the log files going to be stored, creating a backup plan and again how is this stuff going to be organized. Testing the backup and restore process. Moving database locations on the Server after the fact involves Attaching and Detaching. We are not able to use Windows Integrated Authentication and have to use SQL Server authentication. Organizing user groups so you don’t end up with an enormous list of users.

The other huge issue for us has been how to get SQL Server to function on a Novell network. For anyone using a Novell network we have three things that we need to do for clients to connect. Install SQL Native Client from the SQL CD. The specific file name is sqlcli.msi. Make an entry to the host file with a reference to the SQL server and it’s IP address. From the Run dialog box open cliconfig.exe and enable protocols TCP/IP and Named Pipes in that order.

We have run the first user testing at our office successfully and we are starting to run user testing at some of the field offices from Northern to Southern California. So far the testing has been very successful. We have had Access front end/back end configurations in the remote offices and they, as you might imagine have horrible performance. With the tables on SQL Server we are having the user screens appear instantly. An amazing performance improvement.

I continue to be unsure if there are not better ways to accomplish the transition but this was a successful method and is working well so far. I have seen scripts and procedures written for migrating data to SQL Server, but that is beyond my current skill set.

Carla

Reply | Quote

Carla,

Sounds like you have made some great strides and given me some input as well (Thanks). Luckily, we are on an NT environment and so some of the issues you are having dont apply to me. I wouldn’t have thought that the upsize wizard would change data types (again thanks for the input). I do dread, however, having to recreate all the PK, FK, and relationships. That is going to be a pain. Here is a question, does all the access code written continue to reside on the front end or do you need to pass it to the BE as stored procedures? It probably depends on what the code is doing.

Anyway – You are way ahead of me. Good luck and I hope all goes well.

Reply | Quote

Gary,

Bottom line for optimized performance stored procedures are going to be a better choice. SQL views are going to be a better choice than Access queries. Since our current databases are somewhat complex, but not large in size we are not planning on converting much of the existing development other than to insure it is functioning correctly. Because of the size of the databases and limitations on what the future growth in size will be there should not be any performance issues one way or another. Our new development will start to rely more on SQL functionality as our skill set increases. I am glad we are able to transition in stages and not have to reinvent the entire system. My next goal is to start to create cleaner development utilizing SQL tools with Access. Eventually I hope to make the transition to .NET. YIKES! I am encouraged by Charlotte’s comments on making the change and liking .NET.

Carla

Reply | Quote