Importing Table and changing field properties

P

Pele

I was trying to load a table and then set the properties of fields on that
table. Below is the code I was trying to use. Looks like I am having problems
with codes that don't work with Access 2000.

The code is hanging up at this point

Dim fld As DAO.Field

Can somebody look at this code or basically give me a code that will work.
Thanks.

Pele



Sub FixTeam_table()
Const conTable = "1GBBU Teams"
Const confield = "Year"
Const confield2 = "Dept"
Const confield3 = "Team Name"
Const confield4 = "Team Type for CIP"
Const confield5 = "Team Multiplier Type"
Dim tdf As New ADODB.Recordset
'rst.Open (“1GGBU TEAMSâ€,cnn,adOpenKeyset,adLockOptimistic,adCmdTableDirect)
'Dim tdf As TableDef
Dim fld As DAO.Field
Dim fld2 As DAO.Field
Dim fld3 As DAO.Field
Dim fld4 As DAO.Field
Dim fld5 As DAO.Field
Dim dbs As Database

Set dbs = DBEngine(0)(0)
Set tdf = dbs.OpenRecordset("1GBBU Teams")

'Set tdf = DBEngine(0)(0).TableDefs("1GBBU TEAMS")
'Set tdf = DBEngine(0)(0).TableDefs(conTable)
Set fld = tdf.Fields(confield)
Set fld2 = tdf.Fields(confield2)
Set fld3 = tdf.Fields(confield3)
Set fld4 = tdf.Fields(confield4)
Set fld5 = tdf.Fields(confield5)
fld.Properties("Required") = True
fld2.Properties("Required") = True
fld3.Properties("Required") = True
fld4.Properties("Required") = True
fld5.Properties("Required") = True
dbs.Execute "CREATE UNIQUE INDEX idxTeamNameID ON [1GBBU Teams]([Team Name])"
End Sub
 
P

Pele

Can anybody help me. I have now updated the code that I have but it still not
works to completion.

Can somebody look at this code below or basically give me a code that will
work.
Thanks.

Pele


Sub FixTeam_table()
Const conTable = "1GBBU Teams"
Const confield = "Year"
Const confield2 = "Dept"
Const confield3 = "Team Name"
Const confield4 = "Team Type for CIP"
Const confield5 = "Team Multiplier Type"

Dim tdf As New ADODB.Recordset

Dim fld As Field
Dim fld2 As Field
Dim fld3 As Field
Dim fld4 As Field
Dim fld5 As Field

'Dim fld As DAO.Field
'Dim fld2 As DAO.Field
'Dim fld3 As DAO.Field
'Dim fld4 As DAO.Field
'Dim fld5 As DAO.Field
'Dim dbs As dao.Database

Dim dbs As ADODB.Connection

'Set dbs = DBEngine(0)(0)

Set dbs = CurrentProject.Connection

'Set tdf = dbs.Recordset(conTable)

tdf.Open conTable, dbs, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Set tdf = DBEngine(0)(0).TableDefs("1GBBU TEAMS")
'Set tdf = DBEngine(0)(0).TableDefs(conTable)

Set fld = tdf.Fields(confield)
Set fld2 = tdf.Fields(confield2)
Set fld3 = tdf.Fields(confield3)
Set fld4 = tdf.Fields(confield4)
Set fld5 = tdf.Fields(confield5)

fld1.Properties("Required") = True
fld2.Properties("Required") = True
fld3.Properties("Required") = True
fld4.Properties("Required") = True
fld5.Properties("Required") = True
dbs.Execute "CREATE UNIQUE INDEX idxTeamNameID ON [1GBBU Teams]([Team Name])"
End Sub







Pele said:
I was trying to load a table and then set the properties of fields on that
table. Below is the code I was trying to use. Looks like I am having problems
with codes that don't work with Access 2000.

The code is hanging up at this point

Dim fld As DAO.Field

Can somebody look at this code or basically give me a code that will work.
Thanks.

Pele



Sub FixTeam_table()
Const conTable = "1GBBU Teams"
Const confield = "Year"
Const confield2 = "Dept"
Const confield3 = "Team Name"
Const confield4 = "Team Type for CIP"
Const confield5 = "Team Multiplier Type"
Dim tdf As New ADODB.Recordset
'rst.Open (“1GGBU TEAMSâ€,cnn,adOpenKeyset,adLockOptimistic,adCmdTableDirect)
'Dim tdf As TableDef
Dim fld As DAO.Field
Dim fld2 As DAO.Field
Dim fld3 As DAO.Field
Dim fld4 As DAO.Field
Dim fld5 As DAO.Field
Dim dbs As Database

Set dbs = DBEngine(0)(0)
Set tdf = dbs.OpenRecordset("1GBBU Teams")

'Set tdf = DBEngine(0)(0).TableDefs("1GBBU TEAMS")
'Set tdf = DBEngine(0)(0).TableDefs(conTable)
Set fld = tdf.Fields(confield)
Set fld2 = tdf.Fields(confield2)
Set fld3 = tdf.Fields(confield3)
Set fld4 = tdf.Fields(confield4)
Set fld5 = tdf.Fields(confield5)
fld.Properties("Required") = True
fld2.Properties("Required") = True
fld3.Properties("Required") = True
fld4.Properties("Required") = True
fld5.Properties("Required") = True
dbs.Execute "CREATE UNIQUE INDEX idxTeamNameID ON [1GBBU Teams]([Team Name])"
End Sub
Click to expand...
 
D

Douglas J. Steele

Already answered in another newsgroup to which you posted the same question.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Pele said:
Can anybody help me. I have now updated the code that I have but it still
not
works to completion.

Can somebody look at this code below or basically give me a code that will
work.
Thanks.

Pele


Sub FixTeam_table()
Const conTable = "1GBBU Teams"
Const confield = "Year"
Const confield2 = "Dept"
Const confield3 = "Team Name"
Const confield4 = "Team Type for CIP"
Const confield5 = "Team Multiplier Type"

Dim tdf As New ADODB.Recordset

Dim fld As Field
Dim fld2 As Field
Dim fld3 As Field
Dim fld4 As Field
Dim fld5 As Field

'Dim fld As DAO.Field
'Dim fld2 As DAO.Field
'Dim fld3 As DAO.Field
'Dim fld4 As DAO.Field
'Dim fld5 As DAO.Field
'Dim dbs As dao.Database

Dim dbs As ADODB.Connection

'Set dbs = DBEngine(0)(0)

Set dbs = CurrentProject.Connection

'Set tdf = dbs.Recordset(conTable)

tdf.Open conTable, dbs, adOpenKeyset, adLockOptimistic, adCmdTableDirect

'Set tdf = DBEngine(0)(0).TableDefs("1GBBU TEAMS")
'Set tdf = DBEngine(0)(0).TableDefs(conTable)

Set fld = tdf.Fields(confield)
Set fld2 = tdf.Fields(confield2)
Set fld3 = tdf.Fields(confield3)
Set fld4 = tdf.Fields(confield4)
Set fld5 = tdf.Fields(confield5)

fld1.Properties("Required") = True
fld2.Properties("Required") = True
fld3.Properties("Required") = True
fld4.Properties("Required") = True
fld5.Properties("Required") = True
dbs.Execute "CREATE UNIQUE INDEX idxTeamNameID ON [1GBBU Teams]([Team
Name])"
End Sub







Pele said:
I was trying to load a table and then set the properties of fields on
that
table. Below is the code I was trying to use. Looks like I am having
problems
with codes that don't work with Access 2000.

The code is hanging up at this point

Dim fld As DAO.Field

Can somebody look at this code or basically give me a code that will
work.
Thanks.

Pele



Sub FixTeam_table()
Const conTable = "1GBBU Teams"
Const confield = "Year"
Const confield2 = "Dept"
Const confield3 = "Team Name"
Const confield4 = "Team Type for CIP"
Const confield5 = "Team Multiplier Type"
Dim tdf As New ADODB.Recordset
'rst.Open ("1GGBU TEAMS",cnn,adOpenKeyset,adLockOptimistic,adCmdTableDirect)
'Dim tdf As TableDef
Dim fld As DAO.Field
Dim fld2 As DAO.Field
Dim fld3 As DAO.Field
Dim fld4 As DAO.Field
Dim fld5 As DAO.Field
Dim dbs As Database

Set dbs = DBEngine(0)(0)
Set tdf = dbs.OpenRecordset("1GBBU Teams")

'Set tdf = DBEngine(0)(0).TableDefs("1GBBU TEAMS")
'Set tdf = DBEngine(0)(0).TableDefs(conTable)
Set fld = tdf.Fields(confield)
Set fld2 = tdf.Fields(confield2)
Set fld3 = tdf.Fields(confield3)
Set fld4 = tdf.Fields(confield4)
Set fld5 = tdf.Fields(confield5)
fld.Properties("Required") = True
fld2.Properties("Required") = True
fld3.Properties("Required") = True
fld4.Properties("Required") = True
fld5.Properties("Required") = True
dbs.Execute "CREATE UNIQUE INDEX idxTeamNameID ON [1GBBU Teams]([Team
Name])"
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Importing Excel Spreadsheet into Access and Changing Field Propert 7
Switching from Corel Paradox to Access 2010 5
Append data through VBA 2
Import Excel file into table reference by DAO 8
converting Yes/No fields to integer 1
Make custom post form default 4
Fixing my merge file after field changes 1
Hyperlink or Memo? 0

Top