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

changing field data types programmatically

P: n/a
Hi i'm trying to import an Excel Spreadsheet into Access2000 but the data
types for two of the fields in my imported table are being identified
incorrectly. Is there a way of using some VB code in Access to change the
data types for the fields from text to a number field or an Integer field.
I'd be grateful for any advice you could give

Many thanks

David
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"David Gartrell" <da***@davsarg.wanadoo.co.uk> wrote in message
news:d7**********@newsg3.svr.pol.co.uk...
Hi i'm trying to import an Excel Spreadsheet into Access2000 but the data
types for two of the fields in my imported table are being identified
incorrectly. Is there a way of using some VB code in Access to change the
data types for the fields from text to a number field or an Integer field.
I'd be grateful for any advice you could give

Many thanks

David

The solution which gives the most flexibilty is to use Excel automation,
since you can go through the entire contents cell by cell and do any
conversion / error handling on the way. The amount of effort you wish to
spend on this probably depends on whether this is a one-off import or will
be done repeatedly by average pc users (non-nerds).
Nov 13 '05 #2

P: n/a
Unfortunately the columns I want to apply the specific formatting to
will be empty (apart from the field name) at the point that they're
imported into Access. I've already tried using Excel macros to specify
that data type for the fields, but that is lost when I import it into
Access.

I think that the solution for me in this instance lies within Access
but but don't know exactly what to use. I do have some code in another
database that programmatically changes the field name in a table and
I've tried using this as my starting point and have tried a few ideas
but so far without success.

Nov 13 '05 #3

P: n/a
<da***@rightsort.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...
Unfortunately the columns I want to apply the specific formatting to
will be empty (apart from the field name) at the point that they're
imported into Access. I've already tried using Excel macros to specify
that data type for the fields, but that is lost when I import it into
Access.

I think that the solution for me in this instance lies within Access
but but don't know exactly what to use. I do have some code in another
database that programmatically changes the field name in a table and
I've tried using this as my starting point and have tried a few ideas
but so far without success.

So if the Excel sheet contains no data only the field names, are you
basically trying to create a table structure based on this? This is easy
enough to do but I don't know how you can tell what data type to set based
only on the field name. Perhaps you already know the number of fields, the
data types and just need the names.
Anyway, whatever you need to do, it can certainly be done from Access but I
guess there is a bigger picture to this whole process.
Nov 13 '05 #4

P: n/a
I already know what type of data these fields will be populated with,
for example one field will be populated with a date but this will only
be done via certain processes once the table has been imported into
access.

Basically all I want to be able to do is to use VB to set the data type
for certain fields rather than doing it through the design view of the
table. If anyone knows what the code is for this then please could
they let me know.

Thanks

David.

Nov 13 '05 #5

P: n/a
<da***@rightsort.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I already know what type of data these fields will be populated with,
for example one field will be populated with a date but this will only
be done via certain processes once the table has been imported into
access.

Basically all I want to be able to do is to use VB to set the data type
for certain fields rather than doing it through the design view of the
table. If anyone knows what the code is for this then please could
they let me know.

Thanks

David.

You can use data definition queries, for example:
CurrentDb.Execute "ALTER TABLE MyTable ALTER COLUMN MyColumn INTEGER"
To alter existing columns, but you can do more things with the table if you
use DAO. You cannot convert existing columns but as there is no data, you
might as well re-create a blank table with the required types. In the
function below, the field names are taken from the spreadsheet column
headings. The type is set in a fixed manner - ie column 1 is a long
integer, column 2 is text, etc. You call the function like this:
?CreateTable("C:\MyStuff\MyBook.xls","Sheet1","tbl MyTable")

I did send an e-mail offering to send an example mdb - did it not get
through?
Public Function CreateTable(strExcelPath As String, _
strExcelSheet As String, _
strAccessTable As String) As Boolean

On Error GoTo Err_Handler

Dim dbs As DAO.Database
Dim tdfLink As DAO.TableDef
Dim tdfLocal As DAO.TableDef
Dim fldLink As DAO.Field
Dim fldLocal As DAO.Field
Dim strLinkTable As String
Dim strFieldName As String
Dim lngCount As Long

strLinkTable = "~tmp" & Format(Now(), "yyyymmddhhnnss")

Set dbs = CurrentDb

Set tdfLink = dbs.CreateTableDef(strLinkTable)

tdfLink.Connect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & strExcelPath

tdfLink.SourceTableName = strExcelSheet & "$"

dbs.TableDefs.Append tdfLink

dbs.TableDefs.Refresh

Set tdfLocal = dbs.CreateTableDef(strAccessTable)

For Each fldLink In tdfLink.Fields

strFieldName = fldLink.Name

lngCount = lngCount + 1

Select Case lngCount

Case 1
Set fldLocal = tdfLocal.CreateField(strFieldName, dbLong)
tdfLocal.Fields.Append fldLocal

Case 2
Set fldLocal = tdfLocal.CreateField(strFieldName, dbText, 50)
tdfLocal.Fields.Append fldLocal

Case 3
Set fldLocal = tdfLocal.CreateField(strFieldName, dbDate)
tdfLocal.Fields.Append fldLocal

Case 4
Set fldLocal = tdfLocal.CreateField(strFieldName, dbCurrency)
tdfLocal.Fields.Append fldLocal

End Select

Next fldLink

dbs.TableDefs.Append tdfLocal

CreateTable = True

Exit_Handler:

On Error Resume Next

dbs.TableDefs.Delete strLinkTable

Set fldLink = Nothing
Set tdfLink = Nothing
Set fldLocal = Nothing
Set tdfLocal = Nothing
Set dbs = Nothing

Application.RefreshDatabaseWindow

Exit Function

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Function


Nov 13 '05 #6

P: n/a
Bri

David Gartrell wrote:
Hi i'm trying to import an Excel Spreadsheet into Access2000 but the data
types for two of the fields in my imported table are being identified
incorrectly. Is there a way of using some VB code in Access to change the
data types for the fields from text to a number field or an Integer field.
I'd be grateful for any advice you could give

Many thanks

David


I find the easiest way to do this is to create the table first with the
fields difined how I want them and then Import into that table. So,
existing table, vs new table option in the Wizard. With
TransferSpreadsheet just use the existing Table name and it will import
in. If there are any records that can't be imported (eg. you define the
field as Integer and somehow a record has text in it) then an Import
Errors table is created with the record number and reason for failure in it.

--
Bri

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.