Access 2007 >> SQL Server Express (2008 ODBC) VBA ADO DAO

Objective:

Convert a large Access 2007 database front end to work correctly with SQL Server Express2008 (ODBC) as the back end

Background:

I am a very experienced Access and SQL Server database developer who needs help with this conversion because of my personal time constraints.

The original system (before conversion) is

Access 2007 database (front end) with Access 2007 back end (tables only)

I have already converted the back-end tables to SQL Server Express 2008

The Access front-end is now linked to the SQL Server Express 2008 tables,

using ODBC (i.e. Linked tables in front-end use ODBC to communicate with SQL Server)

Now that the front-end is linked to SQL Server, there are things that need fixing

The Challenge Part 1

---------------------------

For database operations from VBA,

mostly ADO code is used (although there may be the occasional DAO)

There are many places the VBA code front-end needs to be modified to work with SQL Server as the back end

For example...

ADO code in the Front-End database that needs modifying to work correctly with SQL Server.

------------------------------------------------------------

#1 Auto-increment columns are not updated until the record is updated.

Cause: After calling [url removed, login to view] when Access is the back-end, the auto increment column is available before the record is updated. This is not true in SQL Server. The new value of the identity column new value is available only after saving the new record.

Resolution: Run the following Visual Basic for Applications (VBA) code before accessing the identity field:

[url removed, login to view]

[url removed, login to view] 0,

[url removed, login to view]

for SQL Server need to add the second line...

[url removed, login to view]

[url removed, login to view] 0

Need to do this wherever there is AddNew in the code

-------------------------

#2 New records are not available.

Cause: When you add a record to a SQL Server table by using VBA, if the table's unique index field has a default value, and you do not assign a value to that field, the new record does not appear until you reopen the table in SQL Server. If you try to obtain a value from the new record, you receive the following error message:

Run-time error '3167' Record is deleted.

Resolution: When you open the SQL Server table by using VBA code, include the dbSeeChanges option, as in the following example:

Set rs = [url removed, login to view]("TestTable", dbOpenDynaset, dbSeeChanges)

-------------------------

#3 Some queries will not let the user add a new record.

Cause: If a query does not include all columns that are included in a unique index, you cannot add new values by using the query.

Resolution: Ensure that all columns that are included in at least one unique index are part of the query.

The Challenge Part 2

--------------------------

Access queries sometimes directly reference the following

- Access Form Field controls

- VBA functions

in the criteria section of some queries

SQL Server cannot use these methods, need to pass parameters, or use Stored Procedures or Pass-Through queries instead.

What are you working with

-----------------------------------

This is a fairly large Access database

Object Type

Table 127 (includes system objects)

Query 527

Form 124

Report 83

Macro 0

Module 25

The front end is under 20MB when compacted (and under 10MB when zipped)

Here is what will be supplied to the winning Freelancer to work with

Access 2007 Front End

SQL Server Back End

Some test instructions will be given

e.g. manual "test harness" activities,

user tasks to complete (sequential user operations)

It is expected you will also create some new tests based on the required changes discovered in VBA code and the queries that require parameters to replace directly referenced VBA functions or Form Objects.

Some example VBA modifications already discovered will be given (as per examples above)

Intellectual Property, and Client Confidentiality

------------------------------------------------------------

The winning Freelancer must respect all Intellectual Property, and provide absolute Client Confidentiality, only dealing with me directly.

Standard contracts for will need to be signed by Empolyer and Freelancer:

[url removed, login to view]

[url removed, login to view]

Escrow Payment

---------------------

Immediately after accepting your offer, I will send you an escrow payment. The project is finished and the escrow release when I confirm successfully testing of the Front End database with SQL Server

Important

------------

I greatly prefer someone who is experienced in this kind of task, not a newbie.

If you are seriously interested in doing this project, send me a private message and explain how you are able to complete the project successfully. I do not consider bids without private messages.

Let me know if you are using any developer tools to help you with the Access VBA.

e.g. FMS tools, MZtools, etc

For example, I have FMS Total Access Analyzer for version 2000 of Access, so I convert the Access 2007 database back to Access 2000 then run the Analyzer on that version to produce documentation.

MZtools is great too (check it out of you've never seen it.... it's free too!)

Upon acceptance, the project must be completed in one week. If you cannot complete it within seven days, you agree to cancel the project and release the escrow.

To be fair to you, the time it takes me to respond to any emails from you or test the delivered database is not counted.

Ongoing

------------

After the successful conversion, there is a good possibility for you receiving ongoing work, developing this database further according to well defined guidelines, in the new environment (Access 2007 front end with SQL Server 2008 back end).

Microsoft Access Arquitetura de software Teste de Software SQL Visual Basic

ID do Projeto: #517203

Sobre o projeto