473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Change Data Type While Importing Excel Table

HeyHey,
I'v got stucked on this problems for days already. What I am trying to do is to change the data type of a sepcific colume within a table which I am importing to my Access database.

My code shows as following:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub cmdUpdateLoanStorage_Click()
  5. DoCmd.TransferSpreadsheet acImport, , "Dynamic Loan Data Storage Trial", "P:\IRP\System Build\IRP 2012\Data Tables\Dynamic Loan Data  Storage.xlsx", True
  6. Call ChangeField("Dynamic Loan Data Storage Trial", "Most Recent Master Servicer Return Date")
  7. End Sub
  8.  
  9. Function ChangeField(ByVal TableName, _
  10.                      ByVal FieldName As String) As Boolean
  11.  
  12.  
  13.    Dim Db As DAO.Database
  14.    Dim strSql As String
  15.  
  16.    strSql = "ALTER TABLE " & TableName & " ALTER COLUMN " & FieldName & _
  17.          " DATE;"
  18.  
  19. End Function
  20.  
  21.  
The table importing part is working but not the ChangeField function. What did I do wrong?? Need advise eagerly

Thanks in advance for any help!!
Jan 11 '12 #1

✓ answered by sierra7

Hi Annabelle,
You are setting up an SQL string but not executing it!

Use NeoPa's syntax (basically inserting square brakets aound object names containing spaces) but add a DoCmd.RunSQL.

Note there was a typo and a '*' should have been a '&' at the end of his line #1.

Expand|Select|Wrap|Line Numbers
  1.  strSql = "ALTER TABLE [" & TableName & "] " & _ 
  2.              "ALTER COLUMN [" & FieldName & "] DATE;"
  3. DoCmd.RunSQL strSql
  4.  
You don't need line #13 to define a DAO database

I seriously suggest youconsider reducing these very long object names!
S7

6 6942
NeoPa
32,556 Expert Mod 16PB
You've got object names with embedded spaces but not allowed for that in the VBA that creates your SQL.

Try changing lines #16 & #17 to :
Expand|Select|Wrap|Line Numbers
  1.     strSql = "ALTER TABLE [" & TableName & "] " & _
  2.              "ALTER COLUMN [" & FieldName & "] DATE;"
NB. This assumes the SQL is basically correct to start with. I only suggest changes to handle the problem asked about.
Jan 11 '12 #2
sierra7
446 Expert 256MB
Hi Annabelle,
You are setting up an SQL string but not executing it!

Use NeoPa's syntax (basically inserting square brakets aound object names containing spaces) but add a DoCmd.RunSQL.

Note there was a typo and a '*' should have been a '&' at the end of his line #1.

Expand|Select|Wrap|Line Numbers
  1.  strSql = "ALTER TABLE [" & TableName & "] " & _ 
  2.              "ALTER COLUMN [" & FieldName & "] DATE;"
  3. DoCmd.RunSQL strSql
  4.  
You don't need line #13 to define a DAO database

I seriously suggest youconsider reducing these very long object names!
S7
Jan 12 '12 #3
NeoPa
32,556 Expert Mod 16PB
Nice catch S7. I've fixed the typo now, but only after you pointed it out. The SQL not ever being run was the big one I missed though {sheepish}.

S7:
You don't need line #13 to define a DAO database
This is technically correct, but typically, when one wants to run some SQL without having to worry about pop-up messages indicating how many records have been effected, the normal way is to use :
Expand|Select|Wrap|Line Numbers
  1. Call Db.Execute(strSQL)
rather than the equivalent :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(False)
  2. Call DoCmd.RunSQL(strSQL)
  3. Call DoCmd.SetWarnings(True)
I imagine the first option was missing from Annabelle's procedure.
Jan 12 '12 #4
sierra7
446 Expert 256MB
Tut tut.
I expect you meant Line #3 to be
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.SetWarnings(True) 
  2.  
S7!
Jan 12 '12 #5
NeoPa
32,556 Expert Mod 16PB
Rofl. Yup. A sloppy day it's been so far :-D

I'll amend it now (And thanks for the heads-up).
Jan 12 '12 #6
sierra7
446 Expert 256MB
Seriously though, it was useful to learn that Db.Execute does not cause messages, because I always use the DoCmd object. This does mean that I have to turn messages Off then On again.

Also, I use ;-
Expand|Select|Wrap|Line Numbers
  1. Application.SetOption "Confirm Action Queries", False '(or True)
which is a bit of a handful to keep typing with two fingers.
S7
Jan 12 '12 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Niraj | last post by:
Hi, I am trying to do bulk insert of binary data (array of bytes) in an Oracle table. The data type of the table is BLOB. I am using Oracle Objects for OLE (OO4O) in C++. The binary data that I...
3
by: rdraider | last post by:
I'm doing a data conversion project, moving data from one SQL app to another. I'm using INSERT INTO with Select and have the syntax correct. But when executing the script I get: Server: Msg...
1
by: Nothing | last post by:
I received a db2 data file that I converted to MS-Access. From Access I ran the upsize wizard to put the tabel in SQL. It put the table in ok but all the data types are nvarchar. I have a couple of...
2
by: deko | last post by:
I have an unbound text box on a form that is set to: Format = Short Date (with the corresponding input mask) When I run this: DoCmd.RunSQL ("UPDATE tblOutput SET ApptStart = " &...
1
by: Ravi | last post by:
HI all, we r using db2 V 8.2, when i am trying to change data type ie. SMALLINT to INTEGER(no data in the table), iam getting this error messege. Pl. help me slove the error. ERROR is ...
3
by: Mejmeyster | last post by:
Hi Everyone, I have a table in which one of the text fields has become too small (since it only holds 255 characters). To remedy that, I'm trying to change the data type of that field to "memo"...
1
by: rharding | last post by:
I have a SQL database which contains a table (TblA) with a field (Fld1) defined as "bigint". When the SQL table is linked to Access the design view of the linked table shows this field as a data type...
2
by: bogie | last post by:
Hello I have some problem to change my column data type in my table property from character(15) to character(100). I use Postgresql 7.4. The problem is I try to make new column with...
14
by: veer | last post by:
Hello expert. I made a program in which the data from an SQL table is transferred to an Excel sheet. When the data type of a field is Integer it works fine, but when the data type of a field is...
8
by: mlwerth | last post by:
Dear Access Group: This is the most basic and most embarrassing of questions, but I cannot find where to change the data type of a text field that I have in Access 2003 to a number field. I've...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.