Drop/Create table in loop

[gammaman]

Hello again. I am trying to improve my program so that temp tables get dropped and recreated dynamically based on a variable name (which is the same as the table name I want to drop and recreate). Currently I have the drop and create statments hard coded at the top of the program like this

Public Sub Import()

'DoCmd.SetWarnings (WarningsOff)
'Establish a Connection to the Current Database
Set db = CurrentDb()
On Error Resume Next
'Drop the tables
DoCmd.RunSQL ("DROP TABLE GPS_NOT_CLIENT")
DoCmd.RunSQL ("DROP TABLE CLIENT_NOT_GPS")
DoCmd.RunSQL ("DROP TABLE ALL_DISCREPANCIES")
'Create the tables
DoCmd.RunSQL ("CREATE TABLE [GPS_NOT_CLIENT]")
DoCmd.RunSQL ("CREATE TABLE [CLIENT_NOT_GPS]")
DoCmd.RunSQL ("CREATE TABLE [ALL_DISCREPANCIES]")

...

I want to improve the proram to do the following but it is not working

Public Sub Import()

'DoCmd.SetWarnings (WarningsOff)
'Establish a Connection to the Current Database
Set db = CurrentDb()

On Error Resume Next
'Open File Dialog to choose file to process
Set fd2 = Application.FileDialog(msoFileDialogFilePicker)
With fd2
'.AllowMultiSelect = True
  .Show
  For Each vrtSelected In .SelectedItems
   
     'MsgBox (vrtSelected)
     Set xlapp = CreateObject("Excel.application")
     Set xlWrkBk = GetObject(vrtSelected)
     If Not xlWrkBk Is Nothing Then
     '   MsgBox (xlWrkBk.Name)
     End If
    
     ws_count = xlWrkBk.Sheets.count
    ' MsgBox (ws_count)
'Application.CutCopyMode = False
   t = 1
  'Loop First Row in Each Excel Worksheet which contains header column names
     For k = 1 To ws_count
        xlWrkBk.Activate
      '  xlapp.Visible = True
        
        Dim tSheetName As String
        
        tSheetName = xlWrkBk.Sheets(t).Name

        DoCmd.RunSQL ("DROP TABLE [ " & tSheetName & " ]")
        DoCmd.RunSQL ("CREATE TABLE [ " & tSheetName & " ]")   

       ....

When I trying running the program with the DROP/CREATE inside the loop, nothing happens

[ssanfu]

From the snippet of code you posted, I see 2 things immediately:

1) You have declared only one variable ("tSheetName ") and
2)that variable is within two For..Next loops. That means that the variable "tSheetName" will be dimensioned every loop (or try to be dimensioned)

All variable declarations should be (IMHO) at the top of the subroutine.

Do you have

Option Compare Database
Option Explicit

as the first two lines of every module?