ActiveConnection = CodeProject.Connection StrSQL = "INSERT INTO " & strTblName & " " _ ' passthrough selection query into the local Access table. ' Create an append query to add the results of the Properties("Jet OLEDB:Pass Through Query Connect String") = gstrODBCConnectĬ "qryPassthrough", cmd Properties("Jet OLEDB:ODBC Pass-Through Statement") = True ActiveConnection = catDB.ActiveConnection ' Create a SQL passthrough query with the SQL string from the ' Remove the passthrough query if it happens to already be present. ' the same column names as the fields in the Access table intoĬodeDb.Execute "DELETE * FROM " & strTblNameĬatDB.ActiveConnection = CodeProject.Connection ' The select statement must return the same number of columns with ' Returns : True when successful, otherwise false.
' the destination table should be cleared before inserting Here is a small function that will allow you to write the pure SQL syntax of all queries in a database to a table. ' bolClearBeforeLoading - flag indicating whether or not MS Access VBA Function Loop through Query Objects and write SQL to Table Continuing the theme of tools that assist the use of MS Access as a platform for transferring data between systems. ' strSQL - Transact SQL select statement identifying the ' Accepts : strTblName - name of the Access table into which the ' ADO passthrough query that is destroyed after use.
' selected values from a SQL Server table by way of a ' Purpose : Inserts records into a MS Access temporary table with Public Function LoadTempFromPassThru(ByRef strTblName As String, _īyRef bolClearBeforeLoading As Boolean) As Boolean This is the procedure that I have been using with error handling removed for clarity: Is this true, or have I not found the proper connection string to make it work? Alternatively, is there an alternative way of loading a subset of SQL Server table or tables in to a local Access table that does not require the overhead of stepping through an ADO record set and writing each record to the associated Access table? It appears to me that an Access passthrough query must have a defined DSN to work. Thus I have tried to convert this to a DSN-less configuration, but I can't seem to make it work. For various reasons, I wish to avoid having to define a DSN on each computer. The current application for which I intend to use this procedure is going to be installed on a number of different computers with different versions of Access. Up until now, this procedure make use of a defined DSN for the ODBC connection which is stored in the global string gstrODBCConnect. These tables are used as row sources for combo boxes when the quantity of data is likely to exceed that which can be used in a value list as row source for the combo box. By subsets, I mean specific columns and/or specific rows. I have been using the following procedure for many years to move subsets of SQL Server tables to an Access front-end application.