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