Changing Field type in existing tables in VBA?

  • Thread starter Thread starter Andy
  • Start date Start date
A

Andy

Is it possible to change a table field type and size of an existing table?

I have tried a number of ways but I keep getting "Invalid use of property"
I am converting a huge mess of tables and have so far have managed to
manually change every number or date field into a fixed length field for
exporting wuith the transfertext command. I have 4 fields that I cannot
change because they are linked everywhere and are primary keys. I decided to
try to copy the tables to "tables_Temp" and then step through the fields
changing the numberes fields using data held in a small conversion table
(referenced by a recordset - rst).

I assume that I am either trying to do the impossible or else doing
something stupid. I have a fall back that is more complicated of creating
new tables via a createtabledef and then appending the data to the new
tables.

Any ideas

code below

As usual many thanks in advance
Andy

With rst
If Left(fldTest.Name, 6) = !ded Then ' is this the correct
collection of tables?
Set fldTest.Type = dbText
Set fldTest.Size = !Size
End If
End With
 
I would not bother changing tables structures to simply make a export. That
is way to radical of a solution. Much like saying to take your pulse, we are
going to send you to the hospital, brining in a whole medical crew, and
perform open heart surgery "JUST" to get your pulse. So, the idea of doing
some table structure modification JUST to obtain a export does not make
sense.

Why not export the table the first time manually. The wizard comes up and
lets you specify the length of each field. You can then save that export
spec (click on the advanced tab..and save as). Now, next time when you
export, specify the name of the spec you created. So, there is no need to
mess with the table structure. Further, to control the data type of the
fields, often you will (and should) use a query, and NOT a base table. So,
between using a query and a export spec, why even bother with trying some
strange approach to modify the table structure?

Last, but not least, you can also just roll your own export routines. They
are quite a bit more work then using the above built in stuff. However, they
can be handy:

A export code shell would look like:

Dim rstRecords As DAO.Recordset
Dim FileH As Integer

Dim strOutFile As String

' define export reocrd fields
Dim txtFirst As String * 12
Dim txtLast As String * 12
Dim txtCity As String * 15

FileH = FreeFile()
Open "c:\mydata.txt" For Output As #FileH

Set rstRecords = CurrentDb.OpenRecordset("select * from contacts order by
lastname")

Do While rstRecords.EOF = False

txtFirst = Nz(rstRecords!FirstName, "")
txtLast = Nz(rstRecords!LastName, "")
txtCity = Nz(rstRecords!City, "")

Print #FileH, txtFirst, txtLast, txtCity

rstRecords.MoveNext

Loop

Close FileH
rstRecords.Close
Set rstRecords = Nothing


End Sub
 
Albert D. Kallal said:
I would not bother changing tables structures to simply make a export. That
is way to radical of a solution. Much like saying to take your pulse, we are
going to send you to the hospital, brining in a whole medical crew, and
perform open heart surgery "JUST" to get your pulse. So, the idea of doing
some table structure modification JUST to obtain a export does not make
sense.
Click to expand...

I know, but I inherited a databse that has huge amounts of data in it, all
in the wrong format for the other database to import. Since there are 50+
tables I did not want to have to set up export specs for them all but I
think I will just have to. It's a long job :-)

Thanks

Andy
 
Back
Top