DoCmd.RunSQL "ALTER TABLE" syntax prblem

Tomba

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.

Dec 29 '06 #1

nico5038

Hmm, I know there's a problem with the ALTER TABLE comand, but try:

strSQL = "ALTER TABLE " & tblname & " ALTER COLUMN F1 DATETIME;"

(Switched the UL into LU)

Also make sure that the column ONLY contains true dates !

Nic;o)

Dec 29 '06 #2

Tomba

First, thanx alot for the help attempt.
I'm pretty stupid for not noticing that i spelled column wrong, but still after i fixed it, i still got the 3293 syntax error (Syntax Error in ALTER TABLE statement)
I'l be glad for future help!
Toimba.

Dec 29 '06 #3

nico5038

OK, try:

strSQL = "ALTER TABLE " & tblname & " ALTER COLUMN F1 DATE;"

Nic;o)

Dec 29 '06 #4

Tomba

Sorry, but i still got a syntax error...
BTW, some things i forgot to mention:
i'm using Microsoft VB 6.3 through ACCESS 2002, I've got no sql server of any kind installed, so i guess i'm using Microsoft Jet SQL 3.0.

Thanks again for the help you tried to provide and for future help:)

Dec 29 '06 #5

nico5038

Strange, as the statement did work for me in Access 2000.
Are you sure that the tablename has no spaces or special characters ?

Try to be sure:
strSQL = "ALTER TABLE [" & tblname & "] ALTER COLUMN F1 DATE;"

Else use a breakpoint and the immediate window to print the strSQL and post that here.

Nic;o)

Dec 29 '06 #6

Tomba

is "-" qulafies as a special character?
Thanx,
Tomba.

Dec 29 '06 #7

nico5038

Had some trouble in Access 2003 with fieldnames with an embedded "-", so try the [ and ] trick and/or use another "all character" tablename to exclude that possibility.

Nic;o)

Dec 29 '06 #8

Tomba

nevermind, it works, thanks a million!
gee, that was exhausting:P
(BTW, i've been doin vb just for 8 days, and sql just for 1, therefore the stupid questions:)

Dec 29 '06 #9

nico5038

It's my motto that there are No Stupid Questions, it's Stupid Not to Ask :-)

I always ask anotherone when I can't find a solution within half an hour, saves a lot of time.

Success !

Nic;o)

Dec 29 '06 #10