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