Add new fields to Table threw queries

[TaintedFlames]

Hi everyone
I just made a revised version of this macro that saves all information to an SQL table. The table is quite large and 6 fields are joined to other tables. Now with my updates, I don't need some fields and I need to add new ones. Since I'm not allowed on the SQL as admin, I thought of adding an table to a network drive. Now what I did is recreate and table threw a query that gets all the useful info on my SQL table and then I have my other fields on the different table. Now I'm not too sure how to syncronise the 2 tables. And, after I made that massive query, access died on me :S.

Anyone have any ideas on a better way of doing this? Cause I'm guessing if it crashed there's something better I could be doing...

Thanks a lot.

Jay

 

[willir]

Jay

If you are trying to create fields in an MS*SQL or Oracle database, you may wish to re-post in another fora. For example, Microsoft SQL Server: Programming Forum

If you are trying to use Access for your call tracking or other database, then perhaps we can help.

...this macro that saves all information to an SQL table. The table is quite large and 6 fields are joined to other tables... I'm not allowed on the SQL as admin

What is your ultimate objective with this project? Are you trying to tease out reports or information not easily accessible to you through the front end application?

You can use Access to present "views" or snapshots of the data. For example, you can build some queries, forms and reports that better presents the information you want.

However, you need to determine if you can grab the data from the production database, or if you have to export/import the data and then massage it. If you have to import the data, then the import process needs to be done periodically.

...Moving on
Okay, the following function will append a field to an existing table. I tested the code, but not thoroughly.

Function MakeMeAField(tblName As String, fldName As String, _
fldType As Integer, Optional fldLen As Integer) As Boolean

On Error Resume Next

Dim dbs As DAO.Database
Dim tdf As TableDef
Dim fld As Field

Set dbs = CurrentDb()

Set tdf = dbs.TableDefs(tblName)

With tdf
    If Nz(fldLen, 0) And fldType = dbText Then
        .Fields.Append .CreateField(fldName, fldType, fldLen)
        MakeMeAField = Err
    Else
        .Fields.Append .CreateField(fldName, fldType)
        MakeMeAField = Err
    End If
End With

dbs.Close
Set dbs = Nothing

End Function

Since I used Resume Next, if the function "fails" and the field is not created, it will return True. This is because Err = 0 if the preceeding code was successful, and 0 = False in the world of VBA.

The way the function works is fairly obvious...
- tblName : Name of existing table
- fldName : Name of new field to be appended to table.
- fldName : A little more tricky, values can be dbTest, dbDate, dbLong, dbInteger, dbBoolean, dbSingle, dbdouble and more
- fldLen : For text fields, you can define the length of the field.

Example in how it works...

Debug.Print ("tblTest", "MyTextFld", dbText, 15)
Debug.Print ("tblTest", "MyIntFld", dbInteger)
Debug.Print ("tblTest", "MyLongIntFld", dbLong)
Debug.Print ("tblTest", "MyDateFld", dbDate)

Richard

 

[TaintedFlames]

Hey Willir,
O.K. I fairly confused now... So the only way this could be done is threw VBA? And to do this is to create the entry in the table. And I would have to run this everytime a request is opened ?

BTW, this is in Access not SQL. If I had access to the SQL tables it would be easier. But I don't, and I would have to ask ITMS to change it, and it would probably be less time consuming to invent my own database language .

Thanks for the help.

Jay

 

[willir]

So the only way this could be done is threw VBA?

You can use Data Definition Language for Access.

I don't need some fields and I need to add new ones ...I have my other fields on the different table. ... And to do this is to create the entry in the table.

Are you talking about adding records, or modifying the table design (fields refer to table design)? My orginal interpretation of your post was that you wanted to add fields to a table.

But it looks more like you want to synchronize the records in the table on the SQL server with the table(s) in your Access database on the network.

If this is your objective, then you need to be able to access the SQL database, perhaps through ODBC.

I made that massive query, access died on me

Access probably ran out of resources. Perhaps TEMP space.

Richard

 

[TaintedFlames]

Hey Richard,

This is my problem updated :
I have an SQL table that I'm not admin. It's used so that users can log requests over the web. Now I add a checklist to our Access form, that will never be used over the web. So it doesn't need to be on the SQL server, and since I'm not admin it makes it's a lot easier. I made the 2nd table on a public network drive and joined the tables in a query. Everything worked through Access, but when you log a request on-line, the 2nd table doesn't get an entry. When that happends, if you try to do the checklist, it starts being weird and nothing works really. Now I match the rows with a request number.

I hope this made everything clearer and thanks for the help so far.


Jay