Option Compare Database
Option Explicit
Sub Make_Table_for_SP(SP As String)
Dim db As DAO.Database, qdf As DAO.QueryDef,
fld As DAO.Field
'
Set db = CurrentDb
Set qdf = db.QueryDefs(SP)
'
Close
Open
"C:\builds\ABC\ABCSQL\scripts\_" & SP & ".sql" For Output As #1
'
drop temp table if exists
Print #1,
"IF OBJECT_ID(N'tempdb..#"
& SP & "', N'U') IS NOT NULL"
Print #1,
"DROP TABLE #" & SP
Print #1,
""
'
create temp table
Print #1,
"CREATE TABLE #" & SP
Print #1, vbTab; "("
For Each fld In qdf.Fields
Print
#1, vbTab; fld.Name; "
"; wm_SqlType(fld.Type);
Select Case fld.Type
Case
10
If
fld.Size < 50 Then
Print
#1, "(50)";
Else
Print
#1, "("; CStr(fld.Size);
")";
End
If
Case
12
Print
#1, "(4000)";
Case
20
Print
#1, "(" & CStr(fld.Size - 3) & ",2)";
End Select
If fld.OrdinalPosition < qdf.Fields.Count
- 1 Then
Print
#1, ",";
End If
Print
#1, ""
Next fld
Print #1, vbTab; ")"
Print #1,
""
Print #1,
"INSERT #" & SP
Print #1,
"EXEC " & qdf.sql
Print #1,
""
'
create select statement
Print #1,
"SELECT"
For Each fld In qdf.Fields
Print
#1, vbTab; fld.Name;
If fld.OrdinalPosition < qdf.Fields.Count
- 1 Then
Print
#1, ",";
End If
Print
#1, ""
Next fld
Print #1,
"FROM"
Print #1, vbTab; "#" & SP
Set qdf = Nothing
Set db = Nothing
Close
End Sub
Function wm_SqlType(TypeNumber
As Long) As String
Select Case TypeNumber
Case 1
wm_SqlType
= "bit"
Case 2
wm_SqlType
= "tinyint"
Case 3
wm_SqlType
= "smallint"
Case 4
wm_SqlType
= "int"
Case 5
wm_SqlType
= "money"
Case 6
wm_SqlType
= "float"
Case 7
wm_SqlType
= "float"
Case 8
wm_SqlType
= "datetime"
Case 9
wm_SqlType
= "binary"
Case 10
wm_SqlType
= "nvarchar"
Case 11
wm_SqlType
= "image"
Case 12
wm_SqlType
= "nvarchar"
Case 15
wm_SqlType
= "uniqueidentifier"
Case 20
wm_SqlType
= "numeric"
End Select
End Function