469,342 Members | 6,660 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,342 developers. It's quick & easy.

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 6565
NeoPa
32,182 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,182 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,182 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

Post your reply

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

Similar topics

2 posts views Thread by Niraj | last post: by
1 post views Thread by Nothing | last post: by
1 post views Thread by Ravi | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.