This code creates two recordset objects, one as an ADO recordset and the other as a
standard Access DAO recordset. It also sets up some string variables to hold the connection
string data.
The connection string is created with the parameters for your server, database, ID, and
password. A connection timeout is set and the connection is opened to the database.
The DAO recordset is set to point to your destination table.
A new ADO recordset object is created and this is used to open the source table in the
SQL Server database using the connection that was created.
The ADO recordset is then iterated through and new records are added into the destination
table using theAddNewmethod.
Finally, the objects are set to Nothing to release memory.
The connection string can also use a DSN:OLEDB;DSN=MyDSN;Uid=MyId;Pwd=MyPassword;In addition, the connection string can be used to connect to another Access database:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;As with the pass-through query, if you are running the ADO query directly on the
external database and you are using SQL Server or Oracle, you need to use a percent sign
(%) instead of a star (*) for wild card characters and use single quotes instead of double
quotes to denote strings. Also, you cannot use IIF. Instead, you must use the notation CASE
WHEN.....THEN.....ELSE.....END.
From this example, you can see how to use ADO to import and manipulate data from
another database, but there are some disadvantages.
One is the fact that passwords are included in the connection strings, which raises security
issues. You can get around this by locking the database up (see Chapter 24 on how to do
this), or you can leave the password out and force the user to enter this. However, this may be
unacceptable if you have this running as an overnight job.
The other disadvantage is that this methodology can be a slow means of populating an
internal Access table. This works well for small tables of under 100 records, but it is
painfully slow in iterating each record where the table size is hundreds of thousands of
records. In Excel VBA, there is aCopyFromRecordsetmethod, which is extremely useful
for this, but nothing like it is provided in the Access Object Model.
To solve this, use the database engine to transfer the data into a temporary table where you
can then write code to deal with it:
Sub FastTransferofData()
Dim Cnct As String, Ccnt 1 as String, Ccnt 2 as String
Cnct 1 = "ODBC;Provider=MDASQL;Driver={SQL Server};"250 Microsoft Access 2010 VBA Macro Programming