Technical Article

Create an Access database from TSQL using ADOX

,

This SP will create an Access database from TSQL using ADOX.
Instead of Access, the Provider can be changed so that it will create *any* kind of database from DBase 3, Oracle, Excel, etc...

CREATE PROCEDURE createAccessDB @dbname nvarchar(128)
--input path+name 
--for example c:\new.mdb
AS
DECLARE @object int
DECLARE @output varchar(255)
DECLARE @hr int
DECLARE @source varchar(255)
DECLARE @description varchar(255)
DECLARE @cmd nvarchar(500)

EXEC @hr = sp_OACreate 'ADOX.catalog', @object OUT
IF @hr <> 0
BEGIN
Print 'sp_OACreate failed.'
goto lblError
END

SET @cmd='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='''+@dbname+''''
EXEC @hr = sp_OAMethod @object, 'Create', NULL,@cmd
IF @hr <> 0 AND @hr<>-2147211470
BEGIN
Print 'sp_OAMethod Create failed.'
goto lblError
END

EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
Print 'sp_OADestroy failed.'
goto lblError
END

return

lblError:
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT,
@description OUT
IF @hr = 0
BEGIN
SELECT @output = '  Source: ' + @source
PRINT @output
SELECT @output = '  Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT '  sp_OAGetErrorInfo failed.'
RETURN
END
/*
based on 
Create an Access 2000 Database Using ADOX
http://www.freevbcode.com/ShowCode.Asp?ID=803
*/

GO

Rate

Share

Share

Rate