How do I change the Field Size property of a linked table using VB?

[permanentlybaffled]

Hi,

This is my first time using a forum, so here goes...

I have an Access app that is in use by several people who know nothing about PC's. I included a few extra columns in each table to store new data elements as needed (the DB is split, so when I write new reports, I can just ship the front end DB).

Unfortunately, I have now tried to create a new field in a form to use a spare number column and found a problem.... The spare number columns have a field size of 'Long Integer', so when I try to use them to store a decimal - the value gets rounded.

I don't want to have to go to every user, making updates to each back end DB in turn, so:

How can I can the 'Field Size' property of a table in a linked DB, using VB in the front end DB, in such a way that a user could execute it (like putting the VB behind a button, for example)????

Any help is gratefully received.... but I'm new to this Access stuff, so please assume I don't know what I'm doing!

G

[ADezii]

Hi,

This is my first time using a forum, so here goes...

I have an Access app that is in use by several people who know nothing about PC's. I included a few extra columns in each table to store new data elements as needed (the DB is split, so when I write new reports, I can just ship the front end DB).

Unfortunately, I have now tried to create a new field in a form to use a spare number column and found a problem.... The spare number columns have a field size of 'Long Integer', so when I try to use them to store a decimal - the value gets rounded.

I don't want to have to go to every user, making updates to each back end DB in turn, so:

How can I can the 'Field Size' property of a table in a linked DB, using VB in the front end DB, in such a way that a user could execute it (like putting the VB behind a button, for example)????

Any help is gratefully received.... but I'm new to this Access stuff, so please assume I don't know what I'm doing!

G

This one was very tricky since you cannot modify the Properties of a Linked Table Field from within the Current Database. I did, however, solve it and the solution is listed below. I am a little short on explanations because I'm kind of tired right now, but if you need any clarification, let me know and I'll get back to you tomorrow. Good luck.

Dim strDB As String, MySQL As String
 
Dim appAccess As Access.Application
 
'Path to External Database
Const strConPathToExtDB = "C:\Test\Testlink.mdb"
 
'Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")
 
'Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToExtDB
 
'tblTopics - Table containing the Field (Long_Integer) to modify
'after executing the SQL Statement, Long_Integer is now a SINGLE Data Type
MySQL = "ALTER TABLE tblTopics ALTER COLUMN Long_Integer SINGLE;"
appAccess.DoCmd.RunSQL MySQL
 
appAccess.CloseCurrentDatabase
Set appAccess = Nothing

NOTE: Substitute your own Path, Table, and Field Name.