Issues with creating second recordset

[abenitez77]

I am having a problem with a recordset not being created. I am able to create a recordset in step 1 and loop thru all column names. I try doing the same with another table, but I first delete all the records then I created 1 blank record...because I thought this might help, but still not able to loop thru all the column names in this second table in Step 2. In step 1, the table is an xls linked table. In step 2, the table is a sql linked table.


rstD = nothing when I check after running this line below: Set rstD = db.OpenRecordset(strsql, dbOpenDynaset)





' STEP 1 ---------------------------------------------------------------------------------
strsql = "Select top 1 * From [MyTable1]"
Set rstF = db.OpenRecordset(strsql, dbOpenDynaset)

'Put the Field names into a string variable
For NumFields = 0 To rstF.Fields.count - 1
tmpstr = tmpstr & rstF.Fields(NumFields).Name & "|"
Next


' STEP 2 ---------------------------------------------------------------------------------
AmzStr = "Delete MyTable2"
Call SQL_PassThrough(AmzConn, AmzStr)

AmzStr = "Insert Into MyTable2 ([agreementid]) Values('1')"
Call SQL_PassThrough(AmzConn, AmzStr)


strsql = "Select top 1 * From MyTable2"
Set rstD = db.OpenRecordset(strsql, dbOpenDynaset)


'Put the Field names into a string variable
For NumFields2 = 0 To rstD.Fields.count - 1
tmpstr2 = tmpstr2 & rstD.Fields(NumFields2).Name & "|"
Next

[abenitez77]

solved my own question....I changed the second recordset to dbOpenSnapshot. But can someone tell me why the dbOpendynset worked on the first recordset and not on the second?

[jonh]

A dynaset is updateable, a snapshot isn't. If the table isn't updateable, creating an updateable recordset will probably fail.

For the table to be updateable from Access it needs a primary key.
If there is no PK, Access should have asked which field to use as the primary key when you linked it.

A linked SQL table will still be listed in the tabledefs collection.


With CurrentDb
For Each t In .TableDefs
For Each f In t.Fields
s = s & f.Name & "|"
Next
Debug.Print t.Name; " contains fields :- ", s
s = ""
Next
End With


Or, if the purpose of this is to fill a list, you could just set the controls rowsourcetype property to 'Field List'.

[OBP]

[QUOTE=jonh;375370]
"For the table to be updateable from Access it needs a primary key.
If there is no PK, Access should have asked which field to use as the primary key when you linked it."

This is NOT correct.
I have just updated a table with no PK with this code
Dim rs As Object
Set rs = CurrentDb.OpenRecordset("matches")
rs.MoveLast
rs.MoveFirst
With rs
.Edit
![Player#] = 10
.Update
.Bookmark = .LastModified
End With
rs.Close
Set rs = Nothing

[jonh]

Or maybe you didn't read the whole thread? :think: Because I'm not sure what posting that code is supposed to prove.

[OBP]

Sorry Jonh you are correct, I missed that he was using 2 different methods to access what appears to be external data.
At 71 maybe I am getting too old for this stuff.

[jonh]

No, no. We all make mistakes. You're doing a great job for the money. :)