Sub SQLTestCreate()
'Used to create a table in SQL datafile
'If the table already exists it will first be deleted
On Error GoTo Err_SQLTestCreate
'define table name
strText2 = "_ABCDEFG_TEST"
'================================
'Check link details for SDABE datafile
strSQL1 = "SELECT tblTableLinks.TableName, tblTableLinks.TableAlias, tblTableLinks.LinkActive, tblTableLinkTypes.LinkType, tblTableLinkTypes.LinkServer, tblTableLinkTypes.LinkDatabase, tblTableLinkTypes.LinkUsernamePassword, tblTableLinkTypes.LinkUsername, tblTableLinkTypes.LinkPassword" & _
" FROM tblTableLinkTypes INNER JOIN tblTableLinks ON tblTableLinkTypes.TableLinkType = tblTableLinks.LinkType" & _
" WHERE tblTableLinks.TableAlias='PupilData';"
Set db = CurrentDb
Set MyRset = db.OpenRecordset(strSQL1, dbOpenSnapshot)
'Identify SQL link parameters for SDABE datafile
strCurrentLink = "ODBC;DRIVER=SQL Server;SERVER=" & MyRset!LinkServer & ";Database=" & MyRset!LinkDatabase & ";UID=" & MyRset!LinkUsername & ";PWD=" & MyRset!LinkPassword
'Debug.Print strCurrentLink
'================================
'Check if qryTempPassthrough already exists
N = 0
For Each qdfTemp In db.QueryDefs
If qdfTemp.Name = "qryTempPassthrough" Then N = 1
Next
'If query exists . . .delete it!"
If N = 1 Then
db.QueryDefs.Delete "qryTempPassthrough"
End If
'================================
Set qdfPassThrough = db.CreateQueryDef("qryTempPassthrough")
'Setup query conditions based on datafile connection & type
qdfPassThrough.Connect = strCurrentLink
'delete table if it already exists
strSQL1 = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[_ABCDEFG_TEST]') AND type in (N'U'))" & _
" DROP TABLE [dbo].[_ABCDEFG_TEST];"
'Run the query..
qdfPassThrough.SQL = strSQL1
qdfPassThrough.ReturnsRecords = False
qdfPassThrough.Execute
'define SQL for pass through query
strSQL2 = "CREATE TABLE [dbo].[_ABCDEFG_TEST](" & _
" [MyText] [varchar](50) NULL," & _
" [MyMemo] [text] NULL," & _
" [MyByte] [bit] NULL," & _
" [MyInteger] [int] NULL," & _
" [MyDateTime] [datetime] NULL," & _
" [MyYesNo] [bit] NULL," & _
" [MyOleObject] [binary](1) NULL," & _
" [MyBinary] [binary](50) NULL" & _
" ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];"
'Run the create table query . . .
qdfPassThrough.SQL = strSQL2
qdfPassThrough.ReturnsRecords = False
qdfPassThrough.Execute
'If strText1 = "" Then
MsgBox "The SQL table " & strText2 & " has been successfully created.", vbInformation, "SQL table created"
'Else
' MsgBox "The SQL table " & strText2 & " was deleted and then successfully re-created.", vbInformation, "SQL table re-created"
'End If
'Delete the temp query & close the database
db.QueryDefs.Delete "qryTempPassthrough"
db.Close
Exit_SQLTestCreate:
Exit Sub
Err_SQLTestCreate:
MsgBox "Error " & Err.Number & ": " & Err.Description & vbNewLine & _
"The SQL table " & strText2 & " could not be created.", vbCritical, "SQL table not created"
Resume Exit_SQLTestCreate
End Sub