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


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


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



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.



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).

Taidot: Microsoft Access, tietojärjestelmäarkkitehtuuri, Ohjelmistojen testaus, SQL, Visual Basic

Näytä lisää: convert access 2007 code sql server, sql server 2008 dao, access 2007 odbc vba, sql server express 2008 access 2007, access 2007 ado sql server, ado vba access odbc examples, odbc vba sql server, access 2007 sql server express, sql server 2008 odbc vba, vba odbc sql server 2008, access 2007 sql server 2008, access vba recordset sql, run time error 3167, vba sql server odbc, sql server 2008 odbc, access 2007 sql, vba odbc sql server, vba pass, access sql server 2008, access front end sql server, query sql server access 2007 vba, ado vba access 2007 example, access 2007 recordset sql server express, vba ado sql server 2008, dao sql server 2008

Tietoa työnantajasta:
( 0 arvostelua ) Melbourne, Australia

Projektin tunnus: #517203

11 freelanceria on tarjonnut keskimäärin %project_bid_stats_avg_sub_26% %project_currencyDetails_sign_sub_27% tähän työhön


Hello - Please see PMB for details of my bid. Thank you. Chandra Ram.

$650 USD 7 päivässä
(166 arvostelua)

Hi Good day, Expert in Access 2007 & MSSQL 2008. Please check PM for more details. And ready to sign NDA. Regards [login to view URL] B.E

$700 USD 7 päivässä
(26 arvostelua)

I am an IT professional with 10 yrs experience. I have very good experience with Ms-Access 2007, SQL Server Express, VBA , ADO & DAO. I will provide quick & professional service.

$700 USD 7 päivässä
(26 arvostelua)

Please see PMB. Thanks, Drew

$550 USD 5 päivässä
(8 arvostelua)

Please read the PM

$300 USD 5 päivässä
(6 arvostelua)

Hello, Please take a look at PM. Thanks

$288 USD 5 päivässä
(14 arvostelua)

Hi, I've good experience in Access to MS SQL Server conversion. I've done a DAO to ADO conversion for a large project. I can do it in 7 days. Thanks, John

$480 USD 7 päivässä
(3 arvostelua)

i am 4 years experience in software development in database MS Access and MSSQL SERVER with different language

$300 USD 7 päivässä
(0 arvostelua)

You are requested to kindly see the PM

$400 USD 5 päivässä
(0 arvostelua)

Please check your PM.

$625 USD 7 päivässä
(0 arvostelua)


$550 USD 8 päivässä
(0 arvostelua)