After performing a left inner join on ms-access using VBA all data types are in general format?

From an excel file I need to grab multiple excel sheets and then perform an left inner join on them. Prior to this users filled out these excel sheets, some of the errors I am checking for is if the cell value is the expected data type. For example, column B is currency (like $10), but a user may have copied-and-pasted a Date field in there (01/4/2022) or a string like "Hello123". These are cases I need to check to make sure the user didn't incorrectly paste values into the wrong cells.

The problem I am facing is, once I perform that left inner join of multiple sheets, it produces a single sheet with all the data. But every column and cell is of type "general". So even if it's a date like 01/4/2022 this gets converted to 44565, if if someone put a currency cell in there like $10, this gets converted to 10.

Is there any way to preserve the cells data types when I perform the left inner join?

Here is the current vba script in ms-access:

' loading in sheet2 to ms-access table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tmp_sheet2", filePath, True, "2. Info!A1:AU" & loan_sheet_last_row
' loading in sheet3 to ms-access table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tmp_sheet3", filePath, True, "3. Info!A1:I" & credit_sheet_last_row
' loading in sheet4 to ms-access table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tmp_sheet4", filePath, True, "4. Info!A1:H" & equity_sheet_last_row
' loading in shset5 to ms-access table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tmp_sheet5", filePath, True, "5. Info!A1:D" & perf_sheet_last_row

'Execute SQL statement to perform the join
Dim strSql As String
strSql = "SELECT sheet2.*, sheet3.*, sheet4.*, sheet5.* " _
        & "FROM ((tmp_sheet2 AS sheet2 " _
        & "LEFT JOIN tmp_sheet3 AS sheet3 ON sheet2.[Unique Transaction ID] = sheet3.[Unique Transaction ID]) " _
        & "LEFT JOIN tmp_sheet4 AS sheet4 ON sheet2.[Unique Transaction ID] = sheet4.[Unique Transaction ID]) " _
        & "LEFT JOIN tmp_sheet5 AS sheet5 ON sheet2.[Unique Transaction ID] = sheet5.[Unique Transaction ID]"

'Create new table with join results
DoCmd.SetWarnings False
Dim tableName As String
tableName = "joined_table"
DoCmd.RunSQL "SELECT * INTO " & tableName & " FROM (" & strSql & ")"
DoCmd.RunSQL "ALTER TABLE joined_table DROP COLUMN [sheet3_Unique Transaction ID]"
DoCmd.RunSQL "ALTER TABLE joined_table DROP COLUMN [sheet4_Unique Transaction ID]"
DoCmd.RunSQL "ALTER TABLE joined_table DROP COLUMN [sheet5_Unique Transaction ID]"

'Add joined_table to ms-access
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM " & tableName)

Dim newSheet As Worksheet
Set newSheet = objFile.Worksheets.Add(After:=objFile.Worksheets(objFile.Worksheets.Count))
newSheet.Name = "2. Joined_sheet"

' Copy header names to new sheet
Dim headerRange As Range
Dim i As Integer
Set headerRange = newSheet.Range("A1")
For i = 0 To rs.Fields.Count - 1
    headerRange.Offset(0, i).value = rs.Fields(i).Name
Next i

' Copy data to new sheet
headerRange.Offset(1, 0).CopyFromRecordset rs

objFile.Save

Solution

One approach to consider is prefixing every cell value with an apostrophe. This causes all inputs to be seen as literal text, not particular data types in spite of cell formatting. The apostrophe will be dropped when data imported and dates will not be converted to number. However, currency symbol will not be retained.

I did a quick test in Excel VBA.

Sub test()
Dim cell As Object
Worksheets("Sheet2").Range("A1").CurrentRegion.Select
For Each cell In Selection
    cell.Value = "'" & cell.Value
Next cell
End Sub