Append using ADO (1 Viewer)

kc1

Registered User.
Local time
Today, 02:51
Joined
Sep 22, 2008
Messages
23
SOS!!!

This is what I want to do, if someone could help me out with the code that would be great.

Assumptions:
2 databases
1. DB_FE
2. DB_BE

2 tables, both the same structure in both of the Databases
1. tbl1_FE
2. tbl1_BE

Using ADO I want to append all records from tbl1_BE in DB_BE to*tbl1_FE in*DB_FE.

I'm pulling my hair out trying to find the correct syntax!!

Many thanks

KC
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:51
Joined
Aug 30, 2003
Messages
36,118
Why don't you post what you have and we'll fix it? It's doing it the hard way, but basically you'd open a recordset on each table. Loop through the source recordset and append to the target using AddNew.
 

kc1

Registered User.
Local time
Today, 02:51
Joined
Sep 22, 2008
Messages
23
Why don't you post what you have and we'll fix it? It's doing it the hard way, but basically you'd open a recordset on each table. Loop through the source recordset and append to the target using AddNew.

I haven't actually written any code yet, I was hoping for some here.

What's the easy way then, I'm all up for making things easy?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:51
Joined
Aug 30, 2003
Messages
36,118
I guess I misunderstood "I'm pulling my hair out trying to find the correct syntax". The easy way is an append query pulling from one table and appending to the other.
 

kc1

Registered User.
Local time
Today, 02:51
Joined
Sep 22, 2008
Messages
23
I guess I misunderstood "I'm pulling my hair out trying to find the correct syntax". The easy way is an append query pulling from one table and appending to the other.

Appending when getting data from a different database? Is that doable?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:51
Joined
Aug 30, 2003
Messages
36,118
Simpler if the table is linked, but still possible with the IN clause if it's not.
 

mdlueck

Sr. Application Developer
Local time
Today, 05:51
Joined
Jun 23, 2011
Messages
2,631
Using ADO I want to append all records from tbl1_BE in DB_BE to*tbl1_FE in*DB_FE.

I'm pulling my hair out trying to find the correct syntax!!

As much as I know, there is no way to use ADO objects and download records for a BE DB (using pass through SQL) and download them to a FE temp table.

I use dual DAO.QueryDef objects for that purpose. The inner QueryDef is configured in Pass Through mode, and the outer QueryDef wrapper selects all records from the inner QueryDef and INSERTs them to a FE temp table.

Update: The blue dbFailOnError was IMPERATIVE in order to get DAO queries to return meaningful error messages. While I do not cover DAO Error Handling in this post, it appears that driving DAO queries with VBA code ALWAYS needs that dbFailOnError attribute to be specified. I have had strange things where Access would even crash upon executing a buggy query with this code. I had to use stepped execution and stop the execution prior to cleaning up the QueryDef objects, run them interactively, THEN I suddenly see the error message. I am not sure if dbFailOnError will cure even that case, but as I said it appears to be a requirement when executing DAO objects with VBA code in order for DAO to return to VBA "what went wrong", and I believe it should be safe to specify even if not specifically reporting the DAO error information in the error handler. You may see this thread for further details about DAO error handling:

Interrogation of DAO object details in the Error Handler
http://www.access-programmers.co.uk/forums/showthread.php?t=246627

Sample code as follows... First is a class function refreshing a table for one class, and follows is the shared code the class leverages to execute the queries:

Code:
Public Function RefreshLocalTmpTbl() As Boolean
On Error GoTo Err_RefreshLocalTmpTbl

  Dim strSQLbe As String
  Dim strSQLfe As String

  'Check if we are suppose to refresh the FE Temp Table
  If flgRefreshFETempTable = False Then
    RefreshLocalTmpTbl = True
    GoTo Exit_RefreshLocalTmpTbl
  End If

  'LOC for Pass-Through query defined in Access VBA code
  [COLOR=Red][B]'Select one or the other query for strSQLbe variable!!![/B][/COLOR]
  strSQLbe = "SELECT [p].[id],[p].[authid],[p].[logtimestamp],[p].[title] " & vbCrLf & _
             "FROM [dbo].[projects] AS [p] " & vbCrLf & _
             "ORDER BY [p].[id];"

  'LOC for Pass-Through query to execute a Stored Procedure query
  [COLOR=Red][B]'Select one or the other query for strSQLbe variable!!![/B][/COLOR]
  strSQLbe = "SET NOCOUNT ON;" & vbCrLf & _
             "DECLARE @id smallint;" & vbCrLf & _
             "SET @id = " & intRecordID & ";" & vbCrLf & _
             "EXEC dbo.clsObjProjectsTbl_RefreshLocalTmpTbl_1ea @id;"


  strSQLfe = "INSERT INTO tmptblqry_projects (id,authid,logtimestamp,title) " & vbCrLf & _
             "SELECT t.id,t.authid,t.logtimestamp,t.title " & vbCrLf & _
             "FROM"

  'Call the shared API and return its return code
  RefreshLocalTmpTbl = dbutils_RefreshLocalTmpTbl("clsObjProjectsTbl_RefreshLocalTmpTbl", strSQLbe, strSQLfe)

  'If that call got a good return code, then set the flag that the FE Temp Table is now valid
  If RefreshLocalTmpTbl = True Then
    flgRefreshFETempTable = False
  End If

Exit_RefreshLocalTmpTbl:
  Exit Function

Err_RefreshLocalTmpTbl:
  Call errorhandler_MsgBox("Class: " & TypeName(Me) & ", Function: RefreshLocalTmpTbl()")
  RefreshLocalTmpTbl = False
  Resume Exit_RefreshLocalTmpTbl

End Function
Code:
'This API populates the FE temp table via a query of the BE database
Public Function dbutils_RefreshLocalTmpTbl(ByVal strQueryAPIName As String, ByVal strSQLbe As String, ByVal strSQLfe As String) As Boolean
On Error GoTo Err_dbutils_RefreshLocalTmpTbl

  Dim daoDB As DAO.Database
  Dim daoQDFbe As DAO.QueryDef
  Dim daoQDFfe As DAO.QueryDef
  Dim strQryNameBE As String
  Dim strQryNameFE As String

  'Define the name for the BE query
  strQryNameBE = "vbaqry" & strQueryAPIName & "_PT"

  'Define the name for the FE query
  strQryNameFE = "vbaqry" & strQueryAPIName

  'Make sure they do not exist, delete the queries we need to build
  Call dbutils_DeleteQueryDef(strQryNameBE)
  Call dbutils_DeleteQueryDef(strQryNameFE)

  'Attach to the FE DB
  Set daoDB = CurrentDb()

  'Build the BE PT Query
  Set daoQDFbe = daoDB.CreateQueryDef(strQryNameBE)
  With daoQDFbe
    .Connect = ObjAppSettings.ODBCConnectString()
    .SQL = strSQLbe
    .Close
  End With

  'Append the name of the BE query onto the FE SQL to complete the FROM clause
  strSQLfe = strSQLfe & " " & strQryNameBE & " AS t;"

  'Build the FE Query
  Set daoQDFfe = daoDB.CreateQueryDef(strQryNameFE)
  With daoQDFfe
    .SQL = strSQLfe
    .Execute [COLOR=Blue][B]dbFailOnError[/B]
[/COLOR]     .Close
  End With

  'Good return code
  dbutils_RefreshLocalTmpTbl = True

Exit_dbutils_RefreshLocalTmpTbl:
  'Delete the queries we just built
  Call dbutils_DeleteQueryDef(strQryNameBE)
  Call dbutils_DeleteQueryDef(strQryNameFE)

  'Clean up the connection to the database
  Set daoDB = Nothing
  Set daoQDFbe = Nothing
  Set daoQDFfe = Nothing

  Exit Function

Err_dbutils_RefreshLocalTmpTbl:
  Call errorhandler_MsgBox("Module: modshared_dbutils, Function: dbutils_RefreshLocalTmpTbl()")
  dbutils_RefreshLocalTmpTbl = False
  Resume Exit_dbutils_RefreshLocalTmpTbl

End Function
 
Last edited:

mdlueck

Sr. Application Developer
Local time
Today, 05:51
Joined
Jun 23, 2011
Messages
2,631
Using ADO I want to append all records from tbl1_BE in DB_BE to*tbl1_FE in*DB_FE.

I'm pulling my hair out trying to find the correct syntax!!

I just encountered a response about another software development product that questioned my "ADO can not execute pass-through queries and download selected records into an Access FE table".

I posted my DAO based suggestion on this thread already.

So am I incorrect and there is an ADO way to do the same sort of thing? If so, please post the answer / sample code. TIA!

I would think it not possible possible since the adoCMD.ActiveConnection string is totally different between sending pass-through queries to the SQL BE DB and connecting to the FE DB. While connected to the BE DB, is there still a way to refer to tables in the FE DB?
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:51
Joined
Jan 20, 2009
Messages
12,849
As Paul siad earlier, inserting into a table into a database from another using ADO can be done by opening a recordset on each table then looping through the source records and writing them to the destination recordset.

Michael, your code seems incredibly complex and obtuse compared to simply linking the table and running an insert query.

Also note that what you are referring to as an "API" is not an API at all but a function.

I am also bewildered by your reference to a Class "clsObjProjectsTbl_RefreshLocalTmpTbl"
 

mdlueck

Sr. Application Developer
Local time
Today, 05:51
Joined
Jun 23, 2011
Messages
2,631
As Paul siad earlier, inserting into a table into a database from another using ADO can be done by opening a recordset on each table then looping through the source records and writing them to the destination recordset.

Then that is interpretive code, not the DB objects doing things directly like my nested DAO.QueryDef solution.

And the person at another forum is incorrect in what they think ADO objects are capable of doing.

Michael, your code seems incredibly complex and obtuse compared to simply linking the table and running an insert query.

Such is how, and the only way how, I have found to issue a pass-through query to the BE DB and directly download the result set into an Access FE temp table.

All of this complexity is hidden in shared code, only needs to exist once in the entire project.


I am also bewildered by your reference to a Class "clsObjProjectsTbl_RefreshLocalTmpTbl"

Such is my naming standard for DAO.QueryDef objects so that there never is a name collision. Name of class underscore name of class method. Simple.
 

RainLover

VIP From a land downunder
Local time
Today, 20:51
Joined
Jan 5, 2009
Messages
5,041
KC

Just so that you are given the best solution why don't you explain what you are trying to achieve and more importantly WHY.

In particular why do you have two Databases with identical structure.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:51
Joined
Jan 20, 2009
Messages
12,849
Then that is interpretive code, not the DB objects doing things directly like my nested DAO.QueryDef solution.

While your nested solution would have some benefit if the BE was on SQL Server the PassThrough aspect is defeated when querying an Access database because the local engine will still be doing the work.

You could get exactly the same result and performance by linking the table and running a normal query.
 

mdlueck

Sr. Application Developer
Local time
Today, 05:51
Joined
Jun 23, 2011
Messages
2,631
While your nested solution would have some benefit if the BE was on SQL Server the PassThrough aspect is defeated when querying an Access database because the local engine will still be doing the work.

My BE DB IS a SQL box!! So the inner query is passed to the SQL BE DB, and the outer wrapper merely maps columns between the inner DAO.QueryDef and Access FE temp table columns.

In the past couple of weeks, I have moved from having Access provide all of the SQL to be executed on the BE DB to having that far simplified - merely executing a Stored Procedure on the SQL box. Some queries were getting very delicate to update as they were such a long SQL string.
 

MylesM

New member
Local time
Today, 22:51
Joined
Aug 31, 2012
Messages
3
Hi Michael

I have downloaded your SQLClient.accdb (converted to 2007).
Connects to SQL Server 2008 aok but hangs with error 3151 when it come to qdfPUBS.Execute
As you can see I have tried changing strSQL to delete from employee but this produces the same result. Any ideas? This should work with 2007 yes?

Cheers
Myles

' Attempt to delete a record that doesn't exist
qdfPUBS.SQL = "DELETE FROM pubs.dbo.employee WHERE lname = 'Ashworth'"
' Simply test one Pass Through query to see that previous
' connect string is still valid (server has not changed)
qdfPUBS.Execute
 

mdlueck

Sr. Application Developer
Local time
Today, 05:51
Joined
Jun 23, 2011
Messages
2,631
Hi Michael

I have downloaded your SQLClient.accdb (converted to 2007).

Must not be talking to me... I do not know what SQLClient.accdb you are talking about. Sorry.
 

MylesM

New member
Local time
Today, 22:51
Joined
Aug 31, 2012
Messages
3
Yes sorry Michael - been a hectic day too many VMs/RDPs open! Bring back DOS, life was far simpler then

Myles Morris
 

mdlueck

Sr. Application Developer
Local time
Today, 05:51
Joined
Jun 23, 2011
Messages
2,631
Bring back DOS

Groan... These ARE the good old days... NO go'n back! :cool:

Actually switching to OS/2 in 1992, I thought I was done with DOS. 4Q 1999 I was asked to look into deploying Windows 2000. The client used a DOS maintenance partition to load the Windows image. Thus, my second time with DOS. Actually, I cooked up my own MS DOS 7 (ala Win98 SE files). Second time was much better than the first, but NO go'n back!!!
 

Users who are viewing this thread

Top Bottom