WJbPcaaD

1Option Compare Database
2Option Explicit
3
4'If Table exists return True else False
5Function TblThere(strTblName As String)
6    If IsNull(DLookup("Name", "MSysObjects", "Name='" & strTblName & "'")) Then
7        TblThere = False
8    Else
9        TblThere = True
10    End If
11End Function
12
13Sub CreateLinkedTbl(strExtDB As String, strSTbl As String, strDTbl As String)
14    'Delete table if existing before linking
15    Call DelTblIfExist(strDTbl)
16    'Create Linked Table
17    DoCmd.TransferDatabase TransferType:=acLink, _
18        DatabaseType:="Microsoft Access", _
19        DatabaseName:=strExtDB, _
20        ObjectType:=acTable, _
21        Source:=strSTbl, _
22        Destination:=strDTbl
23End Sub
24
25Sub DelTblIfExist(strTblName As String)
26    'Check if table not exist then exit sub
27    If Not TblThere(strTblName) Then
28        Exit Sub
29    End If
30    'Close then delete the table
31    DoCmd.SetWarnings False
32    DoCmd.Close acTable, strTblName, acSaveYes
33    DoCmd.DeleteObject acTable = acDefault, strTblName
34    DoCmd.SetWarnings True
35End Sub
36
37Sub DelRecords(strTblName As String)
38    'Check if table not exist then exit sub
39    If Not TblThere(strTblName) Then
40        Exit Sub
41    End If
42    'Close then delete the table
43    DoCmd.SetWarnings False
44    DoCmd.Close acTable, strTblName, acSaveYes
45    DoCmd.RunSQL "Delete * From " & strTblName
46    DoCmd.SetWarnings True
47End Sub
48
49'If the table not existed before then this would keep format of excel file.
50'Hence depends on procedures we should delete table contents first
51Sub ImportExcel(strTblName, strWB, strWSName)
52     DoCmd.TransferSpreadsheet TransferType:=acImport, _
53        SpreadsheetType:=acSpreadsheetTypeExcel12, _
54        tableName:=strTblName, _
55        FileName:=strWB, _
56        HasFieldNames:=True, _
57        Range:=strWSName & "!A1:IU1000000"
58End Sub