By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,806 Members | 1,490 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,806 IT Pros & Developers. It's quick & easy.

Change Data Type While Importing Excel Table

P: 12
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

Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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

Expert 100+
P: 446
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
Expert Mod 15k+
P: 31,489
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

Expert 100+
P: 446
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
Expert Mod 15k+
P: 31,489
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

Expert 100+
P: 446
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.