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

Changing field types with Excel as linked table

P: n/a
I have a VBA subroutine that links an MS Excel spreadsheet and copies
the data into a local table. This process is controlled by a form that
allows users to select the spreadsheet from the file system. I actually
import from two different named ranges from each workbook so for every
import the following subroutine is used twice. The database is used to
collate data collected from different surveys entered by remote users
into an Excel spreadsheet. The whole process runs very well apart from
one annoying intermittent issue.

When the spreadsheet is linked there are a number of critical numeric
fields (long to be exact) but sometimes the field only has no values in
the first 20 or so records the field is designated as Text. This breaks
the import process.

Is there any way to alter attach a schema to the linked table specifying
the field types or will I have to add validation to the insert statement
that copies the data? I would prefer to control the schema as that would
be a simpler process to maintain and at present the insert stateent I
build is very simple. As there are some 40 or so fields I would end up
with a very large insert statement.

ps Ignore the s_log calls, I use a subroutine to log all the activity in
this process for any troubleshooting.
Gavin

Sub s_run_import(s_source As String, _
s_source_table As String, _
s_local_table As String)

Dim db As DAO.Database, tdf As TableDef
Dim s_tmp_table As String, s_sql As String

Set db = CurrentDb()

s_tmp_table = s_local_table & "_tmp"
s_log "Start Data Import"
s_log "Source File: " & s_source
s_log "Source Table: " & s_source_table
s_log "Local Table: " & s_local_table
DoCmd.SetWarnings False
s_log "Create tabledef"
Set tdf = db.CreateTableDef(s_tmp_table)
s_log "Connect to source"
tdf.Connect = "Excel 5.0;HDR=YES;IMEX=2;;DATABASE=" & s_source
s_log "Set Source Object"
tdf.SourceTableName = s_source_table
s_log "Append object"
db.TableDefs.Append tdf

s_log "Set objects to nothing"
Set tdf = Nothing
Set db = Nothing

s_log "Start data copy"
s_log "Clear local table"
DoCmd.RunSQL "Delete * from " & s_local_table

s_log "Transfer from " & s_tmp_table & " to " & s_local_table
s_sql = "INSERT INTO " & s_local_table & " SELECT " & _
s_tmp_table & ".* FROM " & s_tmp_table
DoCmd.RunSQL s_sql

s_log "Remove temporary table " & s_tmp_table
DoCmd.DeleteObject acTable, s_tmp_table

s_log "Clear blank import records"
s_sql = "DELETE * FROM " & s_local_table & " WHERE Site Is Null"
DoCmd.RunSQL s_sql
DoCmd.SetWarnings True

s_log "Transfer to " & s_local_table & " Complete"

End Sub
Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.