Copy DAO.Recordset with SQL Server data to Local table in MS Access 2016

[cage4000]

Hi Access forum peeps, i need your help.

I'm trying to pull records I need from the SQL Server that I have pulled into a DAO.Recordset and save them to the local MS Access Table (Either via append or make table). Any help with this is most appreciated. I have been working on this issue for 2 weeks now:banghead:


here is what i have so far:

Dim MakeAdjTbl As String
Dim dbSQL As DAO.Database
Dim rsMakeT As DAO.Recordset

Set dbSQL = OpenDatabase("", False, False, "Driver={SQL Server Native Client 11.0};Server=L563931F034C53\ICQADB;Database=QA;Trusted_Connection=yes;")


MakeAdjTbl = "SELECT * FROM TBL_ADJ;"


Set rsMakeT = dbSQL.OpenRecordset(MakeAdjTbl, dbOpenDynaset, dbSeeChanges)

‘ Need code to append or make table to local access database table “tblAdjAllDates”

 

[pbaldy]

FYI I moved this out of the moderated code repository forum. If the SQL Server table is linked, the most efficient solution would be an append query.

 

[cage4000]

Thank you pbaldy for putting this in the right thread.

and thank you for your suggestion. i feel that append would be the best as well but i'm stuck on not knowing how to code that into VBA.

 

[pbaldy]

Presuming you have a saved append query:

CurrentDb.Execute "QueryName", dbFailOnError

 

[cage4000]

This is perfect, thank you Paul!

 

[cage4000]

Hi Paul, it looks like i spoke too soon. the command works but it is still trying to make the table on the SQL server backend due to the SQL statement pulling from the DAO.database that is set to the SQL server location.

do you know of any way to run this SQL statement and have the results register to the front end Access DB so that it will save the data the local table?

 

[pbaldy]

Is the SQL Server table linked? I would have eliminated everything you had and executed an append query that drew from the SQL Server table and appended to the local table.

 

[cage4000]

No it is not linked. the only connection i have to the SQL server string through the code. I need to stay away from connecting a linked table. I will not be able to roll this DB out otherwise.

 

[pbaldy]

You must have missed this in my first reply:

If the SQL Server table is linked...

If the table isn't linked, it gets more complicated and less efficient. You can open a second recordset on the local table. Within a loop of the first recordset, use the AddNew method of the second recordset to add each record.

 

[cage4000]

Do you know of the code that would transfer an entire recordset from one to the other recordset without listing all the fields?

this is what i tried before and it froze my system:

'----- Previously listed code------

Dim rs2 As DAO.Database

Set rs2 = CurrentDb

rsMakeT.movefirst
while not rsMakeT.EOF
	rs2.addnew
	rs2!TRANS_ID = rsMakeT!TRANS_ID
	rs2!DC_NUM = rsMakeT!DC_NUM
	rs2!ADJDATE = rsMakeT!ADJDATE
	rs2!DEPT = rsMakeT!DEPT
	rs2!LPNorCARTONID = rsMakeT!LPNorCARTONID
	rs2!AdjCode = rsMakeT!AdjCode
	rs2!AdjName = rsMakeT!AdjName
	rs2!COMPLETED_BY = rsMakeT!COMPLETED_BY
	rs2!LOST = rsMakeT!LOST
	rs2!TotAged = rsMakeT!TotAged
	rs2!Shortage = rsMakeT!Shortage
	rs2!PRFNumber = rsMakeT!PRFNumber
	rs2!Tot = rsMakeT!Tot
	rs2!RGA_NUM = rsMakeT!RGA_NUM
	rs2!REVIEWED_BY = rsMakeT!REVIEWED_BY
	rs2!Reconciled = rsMakeT!Reconciled
	rs2!HHM = rsMakeT!HHM
	rs2.update
    rsMakeT.movenext
wend

rsMakeT.close
rs2.close
set rsMakeT = nothing
set rs2 = nothing

 

[pbaldy]

The second recordset isn't opened correctly; I'm surprised it didn't error. It would look like my template code:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT..."
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

 

[cage4000]

you are right. i was pasting from my previous code file and forgot the set rs2 in there. here is what it look like:

Dim SQLtblAllDates as STRING
Dim db2 as DAO.Database
Dim rs2 as DAO.Recordset

set db2 = currentdb

SQLtblAllDates = "SELECT * FROM tblAllDates;"

Set rs2 = db2.OpenRecordset(SQLtblAllDates, dbOpenDynaset)


rsMakeT.movefirst
while not rsMakeT.eof
	rs2.addnew
	rs2!TRANS_ID = rsMakeT!TRANS_ID
	rs2!DC_NUM = rsMakeT!DC_NUM
	rs2!ADJDATE = rsMakeT!ADJDATE
	rs2!DEPT = rsMakeT!DEPT
	rs2!LPNorCARTONID = rsMakeT!LPNorCARTONID
	rs2!AdjCode = rsMakeT!AdjCode
	rs2!AdjName = rsMakeT!AdjName
	rs2!COMPLETED_BY = rsMakeT!COMPLETED_BY
	rs2!LOST = rsMakeT!LOST
	rs2!TotAged = rsMakeT!TotAged
	rs2!Shortage = rsMakeT!Shortage
	rs2!PRFNumber = rsMakeT!PRFNumber
	rs2!Tot = rsMakeT!Tot
	rs2!RGA_NUM = rsMakeT!RGA_NUM
	rs2!REVIEWED_BY = rsMakeT!REVIEWED_BY
	rs2!Reconciled = rsMakeT!Reconciled
	rs2!HHM = rsMakeT!HHM
	rs2.update
    rsMakeT.movenext
wend

rsMakeT.close
rs2.close
set rsMakeT = nothing
set rs2 = nothing

 

[cage4000]

is there a way to just reference the recordset and transfer to the other recordset without listing all fields?

 

[pbaldy]

Not that I'm aware of. If you can't link to the table you have to do it manually.

 

[cage4000]

No prob, thank you for your time.

 

[pbaldy]

Happy to help. Perhaps somebody will chime in with something I haven't thought of.

 

[Cronk]

is there a way to just reference the recordset and transfer to the other recordset without listing all fields?

If the field count is the same in both recordsets and there is exact correspondence in the order of fields then

rsMakeT.movefirst while not rsMakeT.eof rs2.addnew
for n = 0 to rsMakeT.fields.count-1
rs2= rsMateT
next
rs2.update