Ice123
asked on
Problems with accessing fields in access table
Grrrr.. This one is really annoying me! Here's the problem
I have a central data control bound to a contral table within a database (Employees2.mdb and SF2003 as the table). I have another data control that is used to access other tables in the same database. Basically, as soon as you create a new record in the SF2003 table, the CreateNewTable Function is called (shown below) that creates a new table (surprise surprise!) called FirstNameMiddleNameSurname (e.g. JoeSamBloggs). This is all well and good, but when I try adding a record to that table, it says "Item not in this collection". It is, Cos I put it there! Any Ideas why it cant find the fields in the table that i created only moments before?
Public Function EnterLeaveDates(FromDate As Date, FromTime As String, ToDate As Date, ToTime As String, LeaveEntryNumber As Integer)
MainPage.LeaveControl.Reco rdSource = MainPage.EmployeeControl.R ecordset!F irstname & MainPage.EmployeeControl.R ecordset!M iddleName & MainPage.EmployeeControl.R ecordset!S urname
MainPage.LeaveControl.Data base.OpenR ecordset MainPage.EmployeeControl.R ecordset!F irstname & MainPage.EmployeeControl.R ecordset!M iddleName & MainPage.EmployeeControl.R ecordset!S urname
MainPage.LeaveControl.Reco rdset.AddN ew
MainPage.LeaveControl.Reco rdset!Leav eFromDate = FromDate 'NB - error occurs here!
MainPage.LeaveControl.Reco rdset!leav efromtime = FromTime
MainPage.LeaveControl.Reco rdset!leav etodate = ToDate
MainPage.LeaveControl.Reco rdset!leav etotime = ToTime
MainPage.LeaveControl.Reco rdset.Upda te
MainPage.LeaveControl.Reco rdSource = Testtable
End Function
Sub CreateNewLeaveTable(TableN ame As String)
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Employees2.m db")
' Create a table with three fields and a unique
' index made up of all three fields.
dbs.Execute "CREATE TABLE " & TableName _
& "(LeaveFromDate DATE, LeaveFromTime CHAR, " _
& "LeaveToDate DATE, " & "LeaveToTime CHAR, " _
& "SickFromDate DATE, " & "SickFromTime CHAR, " _
& "SickToDate DATE, " & "SickToTime CHAR, " _
& "Reason Char, " & "DoctorsNote CHAR);"
dbs.Close
End Sub
I would appreciate any suggestions!
Also, is there anyway you can list the fields in a table (i.e. list what it thinks the fields are)
thanks,
I have a central data control bound to a contral table within a database (Employees2.mdb and SF2003 as the table). I have another data control that is used to access other tables in the same database. Basically, as soon as you create a new record in the SF2003 table, the CreateNewTable Function is called (shown below) that creates a new table (surprise surprise!) called FirstNameMiddleNameSurname
Public Function EnterLeaveDates(FromDate As Date, FromTime As String, ToDate As Date, ToTime As String, LeaveEntryNumber As Integer)
MainPage.LeaveControl.Reco
MainPage.LeaveControl.Data
MainPage.LeaveControl.Reco
MainPage.LeaveControl.Reco
MainPage.LeaveControl.Reco
MainPage.LeaveControl.Reco
MainPage.LeaveControl.Reco
MainPage.LeaveControl.Reco
MainPage.LeaveControl.Reco
End Function
Sub CreateNewLeaveTable(TableN
Dim dbs As Database
' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Employees2.m
' Create a table with three fields and a unique
' index made up of all three fields.
dbs.Execute "CREATE TABLE " & TableName _
& "(LeaveFromDate DATE, LeaveFromTime CHAR, " _
& "LeaveToDate DATE, " & "LeaveToTime CHAR, " _
& "SickFromDate DATE, " & "SickFromTime CHAR, " _
& "SickToDate DATE, " & "SickToTime CHAR, " _
& "Reason Char, " & "DoctorsNote CHAR);"
dbs.Close
End Sub
I would appreciate any suggestions!
Also, is there anyway you can list the fields in a table (i.e. list what it thinks the fields are)
thanks,
ASKER
have already got a refence to microsoft ADO xx for DLL and security,
The code you kindly gave me doesnt work though! doesnt like the cat.connection for starters
also, what am I supposed to change adodc2.connection string to?
thanks
The code you kindly gave me doesnt work though! doesnt like the cat.connection for starters
also, what am I supposed to change adodc2.connection string to?
thanks
Sorry
Didn't test it
It should be cat.ActiveConnection.
The adodc2 is the second data control that you referred to. Since it is all one database, I would expect its ConnectionString to be the same as your first ConnectionString.
But...
I've just noticed
Dim dbs As Database
which tells me that you are using DAO and not ADO. My general advice, if you are starting out on DB programming is to switch to ADO.
Do you have Access to look at your new table? If not, can you use the Add-in Visual Data Manager?
Meanwhile, I'll try to dig out some old DAO code which walks through table fields.
Didn't test it
It should be cat.ActiveConnection.
The adodc2 is the second data control that you referred to. Since it is all one database, I would expect its ConnectionString to be the same as your first ConnectionString.
But...
I've just noticed
Dim dbs As Database
which tells me that you are using DAO and not ADO. My general advice, if you are starting out on DB programming is to switch to ADO.
Do you have Access to look at your new table? If not, can you use the Add-in Visual Data Manager?
Meanwhile, I'll try to dig out some old DAO code which walks through table fields.
Sub DAOListTableFields(DB As Database, TableName As String)
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set tdf = dbs.TableDefs(TableName)
For Each fld In tdf.Fields
Debug.Print fld.Name
Next fld
End Sub
I ran your CreateNewLeaveTable and it seemed to work OK.
How do you tell your second data control about the new table? Some .Refresh or .Requery might be needed.
How do you tell your second data control about the new table? Some .Refresh or .Requery might be needed.
Another point, before I go home. This might be a useful and interesting exercise, but I have doubts about a database design that needs a table per employee.
ASKER
Im now utterly confused. Thought I had it cracked, but no! Er ADO / DAO - dont know what the difference is (newbie to this whole thing!). Connectionstrings - I dont have one! All I did was place the data object on a form and link it to the database and a table! My second datacontrol is then told about the new table by
mainpage.leavecontrol.reco rdsource = "JoeSamBloggs"
but when I get to
mainpage.leavecontrol.reco rdset.addn ew 'OK
mainpage.leavecontrol.reco rdset!leav efromdate 'This is where it says it cant find the field.
I still could not get either of the two code sets above to work!
mainpage.leavecontrol.reco
but when I get to
mainpage.leavecontrol.reco
mainpage.leavecontrol.reco
I still could not get either of the two code sets above to work!
OK.
DAO (Data Access Objects) is a perfectly adequate technology, except that it needs a partner, RDO (Remote Data Objects) to use remote databases.
ADO(Active Data Objects) is now the current technology and combines the two.
Much of the code is the same. The first difference that strikes one is that the Database Object has been replaced by a Connection Object, which has a ConnectionString Property. This string is divided into sections by semi-colons. One essential section describes the database location and another describes the data provider, e.g. Access (Jet), Oracle etc. It can also contain password & user information.
This is a ConnectionString:
"Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=D:\My Documents\Allwork\Domestic \freezer.m db;"
It is possible to use both ADO and DAO in the same project (you are mainly using DAO, but have a refererence to ADOX (the DDL & security part). DDL (Data definition language) is used to examine and change the database structures; Tables, Fields - Columns in ADOX's terms, etc.
If you were to mix ADO and DAO, you would have to specify which one you mean when declaring which in objects with a common name:
Dim rs as DAO.Recordset.
or
Dim rs as ADODB.Recordset
The Control sets (Data control, grid etc) for DAO and ADO will not mix and match.
I'll look again at your specific difficulty now & come back later.
DAO (Data Access Objects) is a perfectly adequate technology, except that it needs a partner, RDO (Remote Data Objects) to use remote databases.
ADO(Active Data Objects) is now the current technology and combines the two.
Much of the code is the same. The first difference that strikes one is that the Database Object has been replaced by a Connection Object, which has a ConnectionString Property. This string is divided into sections by semi-colons. One essential section describes the database location and another describes the data provider, e.g. Access (Jet), Oracle etc. It can also contain password & user information.
This is a ConnectionString:
"Provider=Microsoft.Jet.OL
It is possible to use both ADO and DAO in the same project (you are mainly using DAO, but have a refererence to ADOX (the DDL & security part). DDL (Data definition language) is used to examine and change the database structures; Tables, Fields - Columns in ADOX's terms, etc.
If you were to mix ADO and DAO, you would have to specify which one you mean when declaring which in objects with a common name:
Dim rs as DAO.Recordset.
or
Dim rs as ADODB.Recordset
The Control sets (Data control, grid etc) for DAO and ADO will not mix and match.
I'll look again at your specific difficulty now & come back later.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
GrahamSkan - you are a genius! The refresh bit was the one standing in my way! Thank you so much!
I would love to give you some more points for your time, especially describing the basics to me, however, I dont have any left! (Ill give you the 5 that I gained over night!)
I would love to give you some more points for your time, especially describing the basics to me, however, I dont have any left! (Ill give you the 5 that I gained over night!)
Thanks,
No problem with the points. Sorry I didn't see it right away.
No problem with the points. Sorry I didn't see it right away.
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim cl As ADOX.Column
cat.Connection = Adodc2.ConnectionString
Set tbl = cat.Tables("MyTable")
For Each cl In tbl.Columns
Debug.Print cl.Name
Next