How do I run a T-SQL statement from within ACCESS VBA

[tokoloshi]

How do you write a purely T-SQL state such as

• CREATE VIEW,
• ALTER TABLE,
• GRANT/REVOKE CREATE DATABASE

within VBA.

The idea being that I wold like to build up a dynamic sql string, based on user input, then EXEC that string via Access/VBA on the SQL Box.

This is part of the SQL Admin project that I suggested elsewhere in the forum (http://www.access-programmers.co.uk/forums/showthread.php?t=155851)

 

[georgedwilkinson]

Take a look at ADOX. You should be able to run DML commands through an ADOX automation control.

 

[tokoloshi]

Take a look at ADOX. You should be able to run DML commands through an ADOX automation control.

Thanks a million George.

The question is: Can I generate a T-SQL statement like the piece of SQL code in the attached txt files, then execute it on the backend server using ADOX?

Or is there another way to do it?

 

[SQL_Hell]

Another way would be to create a stored procedure with input params, and then dynamically build up your string of sql within the stored proc and execute it.

I tend to use a massive long stored proc which basically has a huge switch statement in it, so if for example a parameter comes in as 'drop' then I write to audit tables, backup the object, email myself a script of the object (for source safe) and then execute the object drop.

 

[tokoloshi]

I have just received a rather nice piece of code from Metra Christofferson at SQLServerCentral. You can can click on the link here to see the complete thread. ( http://www.sqlservercentral.com/Forums/FindPost560449.aspx )

I am copying his code here - after a little bit of cleaning up for readability's sake and then I will attempt to document it later.

I am also including it into the SQL Admin.ACCDB file that I will upload again once I have it working. I have highlighted the bits that are important to me. It would appear that you can construct any T-SQL statement and then pump it through DAO - which for me is a long way towards the solution I am looking for.

Code courtesy of Metra Christofferson

'=========Start here ==========
Public Function CreateNewSQLUser(NewUserName As String, SQLdbName As String)
On Error GoTo PROC_ERR
Dim bsSQL As String
bsSQL = "USE [master] " _
& "if not exists (select * from master.dbo.syslogins where loginname = N'" & NewUserName & "')" _
& " BEGIN " _
& " declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'master', @loginlang = N'us_english'" _
& " if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)" _
& " select @logindb = N'master'" _
& " if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')" _
& " select @loginlang = @@language" _
& " exec sp_addlogin N'" & NewUserName & "', null, @logindb, @loginlang" _
& " exec sp_grantlogin N'" & NewUserName & "'" _
& " exec sp_defaultdb N'" & NewUserName & "', N'master'" _
& " exec sp_defaultlanguage N'" & NewUserName & "', N'us_english'" _
& " END " _
& " USE [master] " _
& " exec sp_grantdbaccess '" & NewUserName & "', '" & Right(NewUserName, Len(NewUserName) - InStr(1, NewUserName, "\")) & "'" _
& " USE [msdb] " _
& " exec sp_grantdbaccess '" & NewUserName & "', '" & Right(NewUserName, Len(NewUserName) - InStr(1, NewUserName, "\")) & "'" _
& " USE [tempdb] " _
& "exec sp_grantdbaccess '" & NewUserName & "', '" & Right(NewUserName, Len(NewUserName) - InStr(1, NewUserName, "\")) & "'" _
& " USE [" & SQLdbName & "] " _
& "exec sp_grantdbaccess '" & NewUserName & "', '" & Right(NewUserName, Len(NewUserName) - InStr(1, NewUserName, "\")) & "'"


Dim qdef As DAO.QueryDef
Set qdef = CurrentDb.QueryDefs("qryTempPassthrough")
qdef.Connect = "ODBC;" & GetConnString()
qdef.ODBCTimeout = 120
qdef.SQL = bsSQL
qdef.Close
Set qdef = Nothing
DoCmd.OpenQuery ("qryTempPassthrough")

PROC_EXIT:
DoCmd.SetWarnings True
Exit Function

PROC_ERR:
If Err.Number = 3325 Or Err.Number = 3146 Or Err.Number = 3270 Then Resume PROC_EXIT
If Err.Number = 3390 Then
MsgBox "This User Name already exists."
DoCmd.Close acForm, "frmAddEdd_NewUser", acSaveNo
Resume PROC_EXIT
Else
MsgBox Err.Number & ", " & Err.Description & " on basSecure in CreateNewUser ", , AppErrTitle
End If
Resume PROC_EXIT
Resume
End Function