Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-02-2008, 01:38 AM   #1
wh00t
Registered User
 
wh00t's Avatar
 
Join Date: May 2001
Location: Central UK
Posts: 264
Thanks: 0
Thanked 2 Times in 1 Post
wh00t is on a distinguished road
MS Access Conversion

Happy new year all!

I am currently working on creating an MS Access > SQL conversion script for our customers to use, the conversion will be run on machines using runtime access so they can not use the upsizing wizard.

The script works correctly making it very easy for the users to convert to SQL, but I have just one problem, I need to keep the same autonumber values in the converted tables, I believe I need to issue the 'SET IDENTITY_INSERT ON' command to the SQL server, but I'm stuck on how to do that from within MS Access.

wh00t is offline   Reply With Quote
Old 01-02-2008, 04:31 AM   #2
wh00t
Registered User
 
wh00t's Avatar
 
Join Date: May 2001
Location: Central UK
Posts: 264
Thanks: 0
Thanked 2 Times in 1 Post
wh00t is on a distinguished road
well that was easy enough (Used 'oDatabase.ExecuteImmediate "SET IDENTITY_INSERT " & tdf.Name & " ON"') But I'm stuck on 1 more thing.

Not all tables have an Identity field and error on the above code if it does not, so i need to see if a table has an Identity value before setting the IDENTITY_INSERT, I can get this from within SQL using 'Select OBJECTPROPERTY(object_id('table_name'), 'TableHasIdentity')' But I don't know how I can retrieve the value from within access, can anyone help with this part?
wh00t is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
upgrading from Access 97 to Access 2000 ghudson General 13 09-15-2009 12:02 AM
Access import function problem murph_123 General 14 08-09-2007 06:27 PM
Microsoft Access 2003 Conversion toolkit knowledge76 General 1 02-15-2005 05:29 AM
[SOLVED] Access 95 to XP Access and Access 95/WinXP problems? Mike375 General 6 05-10-2004 05:55 AM
Access 2000 conversion ? gbanks General 1 03-24-2003 01:07 PM




All times are GMT -8. The time now is 07:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World