Fields with Default Values VBA to pull this info out of a MDB file 2

[nrugado]

I have an access database that I have converted the tables to SQL Server. I am now in the process of changing some field types and have noticed that the default value field that was populated in MS access is not Populated in the SQL server Tables/Fields. Does anyone know where to get this information, if it can even be gotten out of the MDB file? It will save me the time of having to look at each and every field in my Access DB for a Default value. Any help would greatly be appreciated.

Thanks Nick

The response below is what lead me to post this question here.

cbogz (TechnicalUser) Mar 12, 2003
I don't have my Access/VBA books handy, but I think there is a way to do this in VBA. You'd have to cycle through the collection of tables, then through the fields of each table, and look to see if the default value property was set or not. This might be a question you'd want to post to the Access Modules forum.

 

[beetee]

Sub ShowDefaults()
Dim CurTblDef As TableDef
Dim CurDb As Database
Set CurDb = CurrentDb
For Each CurTblDef In CurDb.TableDefs
Dim CurField As DAO.Field
For Each CurField In CurTblDef.Fields
Dim DefValue As String
DefValue = ""
On Error Resume Next
DefValue = Nz(CurField.Properties("DefaultValue&quot, "&quot
If Len(DefValue) > 0 Then
Debug.Print CurTblDef.Name & "." & CurField.Name & " = " & DefValue
End If
Next CurField
Next CurTblDef
End Sub

 

[nrugado]

Thanks... I have inserted that code into a module and I tried to run it but I get a compile error:

It says User-defined type not defined. Can you help with that?
Thanks

 

[scriverb]

Change the Dim statement for the database to the following:
Dim CurDb As DAO.Database

That should get you up and running.

Good job BeeTee. Nice code. Have a star. Bob Scriver

 

[beetee]

Thanks scriverb, but it was no big thing; we all keep knocking out the same stuff over and over. It's a bit minor for a FAQ I guess.

 

[scriverb]

Even though it is no big thing for some of us it is the kind of code that a lot of Tek-Tip participants are really glad to see and be able to use. That's the kid of stuff that many of them would never be able to come up with and I was just complimenting you on making it available to them. Bob Scriver

 

[nrugado]

Thanks for you help, however, I am still getting errors. I replaced the third line of code "dim curdb as database" with the suggested dim curdb as dao.database and the next error that came up was a compile error for the 2nd line of code that states "dim curtbldef as tabledef". Any help you can provide would be greatly appreciated.

Thanks Nick

 

[scriverb]

Put the DAO in front of that one also.
Dim curtbldef as tabledef

Bob Scriver

 

[nrugado]

Ok so here is the code as it stands now....
I am still getting an error message on the 2nd line.
Maybe I am doing this wrong... I have opened up a module in ms access pasted / modified this code and then clicked on run sub/ user form or the play button at the top of the ms bisual basic window or do I need to run this code an entirely different way. I know that you are both very busy, and I thank you for your time. Were either of you successful in running this code in an access db that you have locally?

Sub ShowDefaults()
Dim CurTblDef As dao.TableDef
Dim CurDb As dao.Database
Set CurDb = CurrentDb
For Each CurTblDef In CurDb.TableDefs
Dim CurField As dao.Field
For Each CurField In CurTblDef.Fields
Dim DefValue As String
DefValue = ""
On Error Resume Next
DefValue = Nz(CurField.Properties("DefaultValue&quot, "&quot
If Len(DefValue) > 0 Then
Debug.Print CurTblDef.Name & "." & CurField.Name & " = " & DefValue
End If
Next CurField
Next CurTblDef
End Sub

 

[beetee]

You probably need to set a reference to the "Microsoft DAO 3.6 Library".

Open the VB window (e.g. by opening a module)

Go to Tools:References

Scroll down until you find the aforementioned reference, and click it.

 

[nrugado]

Thank you .... We are one step closer. I clicked on Run and it flashed on the title bar to say running and then it finished. There was no output to any report or printer. Am I using the code properly? I should be able to just run this in the visual basic editor right?

 

[scriverb]

Check the Debug window for your output. The code prints to that window. Bob Scriver

 

[beetee]

Press ^G, or poke around the menu system until you find the 'immediate' window. You want to be looking at a module (by opening it) when you do this.

While your at it, it would be a good time to get acquainted with the debugger.