convert database to SQL server

[Guest]

I have an access database. Since the DB size reach to 2G, I convert the data
part to SQL server. And linked the tables back to MDB file. The question is
the autonumber column which is correspond idenity column in SQL server.

When I insert a record to a table that contains autonumber column. The
number is generated after I leave the record. While before, the number is
generated while the record has data (first keystrok in any field).
Because I need do something else to this autonumber column before leaving
the record. Rigth now, the program couldn't work now.

The program is in hurry and do not have time to fix. I wonder if there is
any quick solution for this.

By the way, is access 2007 still has the database size limited to 2G?

Thanks

 

['69 Camaro]

Hi.

While before, the number is
generated while the record has data (first keystrok in any field).
Because I need do something else to this autonumber column before leaving
the record.

That's how SQL Server works. The work-around is to save the record, then
use SQL Server's @@Identity function to retrieve the latest Identity value
in the table. Please see the following Web page for Access MVP Allen
Browne's example code for this:

http://groups.google.com/group/microsoft.public.access.queries/msg/764193b98c2d20ef?hl=en&

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[aaron.kempf]

Access 2000, 2002, 2003 and 2007 support larger database sizes.

if you're using SQL Server you should be using Access Data Projects;
which as a whole-- do not have a 2gb limit.

the SQL 2005 Express edition-- which plays nice with Access 2007--
that has a 4gb limit in the free version I believe.