473,836 Members | 1,261 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Change Data Type While Importing Excel Table

12 New Member
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
6 6996
NeoPa
32,584 Recognized Expert Moderator MVP
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 Recognized Expert Contributor
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,584 Recognized Expert Moderator MVP
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 Recognized Expert Contributor
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,584 Recognized Expert Moderator MVP
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 Recognized Expert Contributor
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
6884
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 have to insert is in the form of byte array. My problem is that the bulk inserts happen only as long as the length of array is less than 4K. Any data more than 4K gets truncated. Is it possible to do bulk inserts in a BLOB field with data more...
3
12129
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 8114, Level 16, State 5, Line 1 Error converting data type varchar to float. Is it possible to change the data type during the INSERT INTO statement?
1
4434
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 the fields that are cureny and some that are numeric. I need to change the data types from nvarchar to numeric type fields. I am new to SQL so I do not know all the commands. How do I change the data type? Michael Charney
2
5304
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 = " & Me!txtApptStart) the value in the table is only a time value, or some other incorrect date.
1
2723
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 SQL0443N Routine "ALTOBJ" (specific name "") has returned an error SQLSTATE with diagnostic text "SQL1013 Reason code or token: dbname ". SQLSTATE=38553
3
3299
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" in design mode. However, when I go to save the new settings of my table, the error message "Record is too large" pops up and won't allow me to save the table. What am I doing wrong? I work with Access 97 in Windows XP. Thanks for your help!
1
4596
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 of "Text". I have another linked table (TblB) with a field (Fld2) defined as "int". I need to join TblA to TblB using Fld1 and Fld2 as the join. This of course does not work. There is a data type mismatch error generated. I do not have the...
2
5778
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 charcater(100) then i copy the data from teh original column to the new column, then i try to drop the column but i get the following ERROR : Cannot drop table invention column invention_cite because the other objects depend on it. Is any body can help me to...
14
1763
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 "numeric" it produces the error "APPLICATION ERROR OR OBJECT DEFINED ERROR" but I can't change the data type of the table from Numeric to Integer. Please provide some idea. Thanks in advance.
8
3279
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 searched high and low through help databases and on the internet. The directions say to : Open the table in Design view Click the Data Type column of the field you want to change, click the
0
9813
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9661
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10536
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7778
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6976
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5815
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4446
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4005
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.