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.
9 18465
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)
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.
OK, try:
strSQL = "ALTER TABLE " & tblname & " ALTER COLUMN F1 DATE;"
Nic;o)
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:)
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)
is "-" qulafies as a special character?
Thanx,
Tomba.
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)
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:)
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)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: astpaul |
last post by:
Hi, I've got DMX2004 with Impakt 2.6.2 and MySQL 4.0.16 with ODBC
3.51.06. I am trying to get tables descriptions in order to alter
their columns. As following MySQL manual, I use for instance...
|
by: Robin Tucker |
last post by:
I have some code that dynamically creates a database (name is @FullName) and
then creates a table within that database. Is it possible to wrap these
things into a transaction such that if any one...
|
by: cainlevy |
last post by:
I'm wondering if there's any way to speed up create table queries?
Besides upgrading hardware, that is. The very simplest table creation
query, "create table table1 ( field1 INT(10))" is taking...
|
by: Prince Kumar |
last post by:
Is there any way I can define an Unique constraint or unique index
which allows more than one null values for the same column combination
in DB2?
ie, If my index is defined on (col3, col4) where...
|
by: Hans-Dieter Franz |
last post by:
Hello,
I have a problem with a Java user-defined stored procedure
and the "alter sequence" statement. I use DB2 8.1.2 and had the
same problem with 8.1.0, but not with 7.*.
I get the following...
|
by: Daniel Chou |
last post by:
Hello,
I have two questions about "not logged initially":
1. Before using "alter table tbname activate not logged initially",
should the table be created with "not logged initially"?
2....
|
by: Shea Martin |
last post by:
I am moving my database from a machine running 7.4 to a machine running 7.2. My
sequences are not out of wack, is there a way to set the current value of a
sequence? I though ALTER SEQUENCE would...
|
by: haiminnu |
last post by:
I have created Two tables
1]EmployeeTable
--------------------------
EmpID
EmpName
AccessLevelID ...
|
by: jbt007 |
last post by:
All,
Access 2003 - WinXP
I thought this would be a no brainer, but it seems to be a perplexing problem. I have a simple table I use for importing several text reports, use VBA to run through...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |