I have a really annoying problem, which i tried solving for almost 3 days now. (I googled in any variation i could think of, but i couldn't find anything that will help my stupid me understand:\)
I'm trying to use a ALTER TABLE ALTER COULMN to change the datatype of a certain column in a table i'm importing by code from an .xls to my access database.
I'll cut to the chase and paste the code:
Option Compare Database
Option Explicit
Private Sub cmdImport_Click()
Dim myfile
Dim mypath
Dim sheetnum
Dim sheetname
mypath = "x:\History\"
Do
myfile = Dir(mypath & "*.xls")
If Right(Left(myfile, Len(myfile) - 4), 2) = "04" Then
sheetnum = 6
Else
sheetnum = 1
End If
Do
If sheetnum < 10 Then
DoCmd.TransferSpreadsheet acImport, 8, "" & Left(myfile, Len(myfile) - 4) & "-0" & sheetnum, mypath & myfile, False, "0" & sheetnum & "!B:H"
sheetname = Left(myfile, Len(myfile) - 4) & "-0" & sheetnum
Else
DoCmd.TransferSpreadsheet acImport, 8, "" & Left(myfile, Len(myfile) - 4) & "-" & sheetnum, mypath & myfile, False, sheetnum & "!B:H"
sheetname = Left(myfile, Len(myfile) - 4) & sheetnum
End If
UpdateTable (sheetname)
sheetnum = sheetnum + 1
Loop Until sheetnum = 13
myfile = Dir
Loop Until myfile = ""
End Sub
Function UpdateTable(tblname As String)
Dim strSQL As String
strSQL = "ALTER TABLE " & tblname & " ALTER COULMN F1 DATETIME;"
DoCmd.RunSQL strSQL
End Function
I thanx in advance to anyone who'll be able to help me!
Tomba.