Saturday, February 25, 2012

MS-SQL SQL to Access SQL

I have some SQL that looks like this (see below) it is for MySQL and MS-SQL (so I am told) however I need to impliment the data structure in Access. I have written a VBscript Class that converts and adds all the tables etc but the constraints are not going to work.

My script can strip out the "[dbo]." that access doesn't need but what I need to get any further is to reconstruct this SQL (See below) so that I have SQL that does the same thing in Access.

Once there I can create a few string manipulations to transform it and bingo job done.

I need some help as I am very weak in the ALTER TABLE department.
ALTER TABLE [dbo].[openwiki_macrohelp] ADD
CONSTRAINT [DF__openwiki___macro__7908F585] DEFAULT (1) FOR [macro_builtin],
CONSTRAINT [DF__openwiki___macro__79FD19BE] DEFAULT (0) FOR [macro_numparams],
CONSTRAINT [DF__openwiki___macro__7AF13DF7] DEFAULT ('No description available') FOR [macro_description],
CONSTRAINT [DF__openwiki___macro__7BE56230] DEFAULT ('None') FOR [macro_param1],
CONSTRAINT [DF__openwiki___macro__7CD98669] DEFAULT ('None') FOR [macro_param2],
CONSTRAINT [DF__openwiki___macro__7DCDAAA2] DEFAULT ('None') FOR [macro_param3],
CONSTRAINT [DF__openwiki___macro__7EC1CEDB] DEFAULT ('None') FOR [macro_comment]
GOI can't see why you'd need any code in MS-Access. Just set the default values specified using the MS-Access GUI, and it should handle the rest for you.

While MS-Access doesn't scale well compared to MS-SQL, it certainly is easier to use!

-PatP

No comments:

Post a Comment