force a field to be of a certain datatype through code

[neemi]

I am have written a function to import excel spreadsheets into access. The fields on the spreadsheet could differ therefore the table is created on import with transferspreadsheet command and is called tblRecon.

The problem is this table is linked to another table in the database after it is imported and certain fields are importing as the wrong datatype therefore the there is a datatype mismatch.

Is there a way I can force a field to be of certain datatypes on the import without using the Import Spec as I know the import spec defines the what fields are imported and my spreadsheets the fields on the spreadsheet could vary.

The user must have at least for defined fields imported. For example: VisitID, On the db this field is Text but when impoted it is Number. Can I have automate it to be text so the query works.

Help appreciated.

Cheers in advance,
Neemi

 

[JeffTullin]

You could try writing a view that exposes all the fields as text, then link to the view.
Alternatively, you could use a query to convert the imported table into a table holding just text fields.

 

[TonyJollans]

Hi neemi,

If you really can't use an import spec, you can import all your fields as text and then change individual ones witha bit of DDL ..

[blue][tt]ALTER TABLE ImportedTable ALTER COLUMN ColumnToChange TEXT[/tt][/blue]

Note that this doesn't work prior to 2000, and it is a great deal harder to do in 97.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[ordendelfai]

Deciding to throw the dice and add my question to this post as it is almost exactly the situation I have <

However, I am using Access 97, and I MUST be able to convert the data type AFTER the data has been imported. I have to use import specs to import everything as text for complicated reasons.

I have searched extensively and it seems that it can be done with DAO, but I cannot figure it out.

Please help

~Joel

 

[ordendelfai]

Hoping someone might see this still, anyone know how?

 

[neemi]

so you have an import spec set up and once the data is imported into a table you want to change the data types?

What I have done is create a table with the names of the imported fields in them and the datatypes I want them to be after it is imported.

then i run the function with the following code in it:

Dim strAlterColumn
Dim strAlterType

rsLink.Open "tblDataTypes" CurrentProject.Connection, adOpenKeySet, adLockOptimistic

If rsLink.RecordCount > 0 Then

rsLink.MoveFirst
Do Until rsLink.EOF

strAlterColumn = rsLink![Name]
'// Where Name is the name of the field imported
strAlterType = rsLink![TYPE]
'// Where Type is the datatype I want to change it to
DoCmd.RunSQL "ALTER TABLE tblReconcilliation ALTER COLUMN " & strAlterColumn & " " & strAlterType
rsLink.MoveNext
Loop

Else
'// do nothing
End If
rsLink.Close


You need to remember that the datatypes need to be in sql syntax. ie if you would change to Yes/No type in table properties this is of a "bit" datatyoe in sql etc.

Let me know if this is what you wanted.

Hope it helps.

Cheers,
Neemi

 

[neemi]

sorry "tblReconcilliation" is the name of the table where you are updating the datatypes.

Let me know how u get on.

Cheers,
Neemi

 

[TonyJollans]

Hi ordendelfai,

Unfortunately ALTER COLUMN is not available in 97 - and I don't know any way to actually alter a column. What you CAN do is to create a new column of the type you want and then run an SQL UPDATE to fill that from the original column, and then delete the original column - far from ideal I know, but it should work.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[BobJacksonNCI]

neemi and Joel,

I haven't needed to perform imports for some time...

But, when I ran into the datatype issue here, I created a dummy row with data in each column representative of the datatype I needed - because the import guesses based on the contents of the first row. I inserted my dummy row as the first row of the file, imported, and then deleted the dummy row.

Not elegant, but effective. And verison universal, I think.



HTH,
Bob

 

[ordendelfai]

Thanks for the ideas, I was hoping to avoid using the add column/update or the new table workaround, but either will work. If anyone reads this someday and knows the way to update the data type with VBA code in access 97 (I think it can be done with DAO somehow), please post it here!

~Joel

 

[ordendelfai]

As a follow up, my final solution is below. Thanks for the idea Tony:

For the columns I needed to change the data type, I changed the column names in my import specs so that the word "old" was after the original name I was using.

I then used the code below to add the columns with the original field name and the proper data types:

Sub AddFields()

'This function adds the proper field names to the imported table with proper data types.
'This will then allow Access to perform and update into the new fields with the "old" field text data, in essence "changing" the data type.

Dim dbs As Database, tdf As TableDef
Dim fld As Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs!tbl_Link_RevenueReport 'Change to your table name

'Adds a column titled "Revenue"
Set fld = tdf.CreateField("Revenue")
fld.Type = dbCurrency
'Append fld to Fields collection.
tdf.Fields.Append fld

'Adds a column titled Total_Members
Set fld = tdf.CreateField("Total_Members")
fld.Type = dbDouble
'Append fld to Fields collection.
tdf.Fields.Append fld

Set dbs = Nothing
End Sub

Then I use an update query to update these two columns:

DoCmd.RunSQL "UPDATE tbl_Link_RevenueReport SET tbl_Link_RevenueReport.Revenue = [RevenueOld]," & _
"tbl_Link_RevenueReport.Total_Members = [Total_MembersOld];"

I could have then dropped the "old" columns, but since I delete the whole imported table after I am done with it, there was no need.

Hope this helps some poor soul in the future who's company is still using Access 97!

~Joel

 

[Mystic1112]

Ordendelfai,
Unfortunately, the two work arounds mentioned in this thread are the best solutions. Using DAO you can only modify the datatype of a field before you append it to the table. Microsoft recommends using the DDL as introduced in Jet 4.0 to modify field datatypes per Knowledge Base article 249682.

Erika

 

[ordendelfai]

Thank you Mystic, I read up on the Jet 4.0 earlier, but unfortunately my company does use it. We have Access 97 and are still using Jet 2.x :-(

This workaround worked fine however, so I happy ;-)

~Joel