I want to open a new (blank) ADO recordset and put data into it from another recordset and a field whose data will be generated programatically. I don't want to build the data into an actual table as this creates exclusive locks on the table (one user only). Is there any way to do this? The .AddNew method won't operate on a recordset object that hasn't been opened and I can't figure out how to open a recordset on a nonexistant table.
Build a recordset with no underlying table
- Thread starter Cynoclast
- Start date
Here are the tables:
Source table:
Field1, field2, ...., Field6
new recordset:
Date, Field1, Field2, ... Field6
Where Date is generated in the program (in a For loop) and appended to each row from the source table into the floating (no underlying table/query) recordset I can't figure out how to make.
Source table:
Field1, field2, ...., Field6
new recordset:
Date, Field1, Field2, ... Field6
Where Date is generated in the program (in a For loop) and appended to each row from the source table into the floating (no underlying table/query) recordset I can't figure out how to make.
Milothicus
Registered User.
- Local time
- Today, 10:23
- Joined
- Sep 24, 2004
- Messages
- 134
if you've split the database, and each user has their own copy of the front end on their computer, you could use a temp table in the front end, and then an append query to move the data to the back end when necessary. that way, each person has their own temp table, so no multi user use.
if it's just one file that everyone's opening, splitting it may be a good idea anyway... or you could have temp tables created with names that vary depending on the user, or you could produce a table name programmatically with a random number for the name, store that number as a variable, so you can retrieve the data later, and delete the table when you're done with it. hopefully the same number won't be created by two users using the database at the same time.
that's if there's no way to store data without putting it in a table......
just some ideas.
if it's just one file that everyone's opening, splitting it may be a good idea anyway... or you could have temp tables created with names that vary depending on the user, or you could produce a table name programmatically with a random number for the name, store that number as a variable, so you can retrieve the data later, and delete the table when you're done with it. hopefully the same number won't be created by two users using the database at the same time.
that's if there's no way to store data without putting it in a table......
just some ideas.
Splitting the database isn't an option due to deployment issues for the client. If they have their own copy on their own computer and can move it to the gods know where then releasing a new version becomes a problem. All the users are accessing the same file. I think the best solution so far is
so it looks like "tbl23423smith_jix" or something like that. Then just delete it when they're done. I know creating & deleting tables is bad practice in Access because it causes the filesize to grow but I can set it to compact nightly before it does its backend stuff.
Code:
"tbl" & PID & Username & Host
Surjer
Registered User.
- Local time
- Today, 10:23
- Joined
- Sep 17, 2001
- Messages
- 232
Dim rs As ADODB.Recordsetrs
set rs = New ADODB.Recordset
With rs
.Fields.Append("MyStringField", ADODB.DataTypeEnum.adBSTR, 255)
.Fields.Append("MyStringField2", ADODB.DataTypeEnum.adBSTR, 255)
.Fields.Append("MyDateField", ADODB.DataTypeEnum.adDate)
.CursorType = ADODB.CursorTypeEnum.adOpenStatic
.LockType = ADODB.LockTypeEnum.adLockOptimistic
.Open()
End With
set rs = New ADODB.Recordset
With rs
.Fields.Append("MyStringField", ADODB.DataTypeEnum.adBSTR, 255)
.Fields.Append("MyStringField2", ADODB.DataTypeEnum.adBSTR, 255)
.Fields.Append("MyDateField", ADODB.DataTypeEnum.adDate)
.CursorType = ADODB.CursorTypeEnum.adOpenStatic
.LockType = ADODB.LockTypeEnum.adLockOptimistic
.Open()
End With
