DisplayName Property of ListObject. Creates or returns a named range for the
inserted data.
Refresh Causes Excel to connect to the data source, execute the SQL query
again, and return data to the range that contains the QueryTable
object. The QueryTable object doesn??™t communicate with the data
source once data is inserted unless this method is called.
Simplifying the Code
The code Excel generates, while accurate, is certainly not something one would want to
maintain. And you can forget about flexibility. The Array function used to pass in the connection
string and database information is one scary looking piece of code. One of the first
things we can do to simplify this is to create our own connection string and store it in a variable.
This will give us the advantage of easier maintenance. Create a new function in
Module1 and name it GetAccessData2. Paste the code from GetAccessData into it, and then
add the following declaration and code (be sure to change the path to the Northwind 2007
database to your location):
Dim sConnString As String
sConnString = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";" ??
& "User ID=Admin;" ??
& "Data Source=C:\projects\Excel2007Book\Files\Northwind 2007.accdb;" ??
& "Mode=Share Deny Write;Extended Properties="""";" ??
& "Jet OLEDB:System database="""";" ??
& "Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";" ??
& "Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;" ??
& "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;" ??
& "Jet OLEDB:New Database Password="""";" ??
& "Jet OLEDB:Create System Database=False;" ??
& "Jet OLEDB:Encrypt Database=False;" ??
& "Jet OLEDB:Don't Copy Locale on Compact=False;" ??
& "Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;" ??
& "Jet OLEDB:Support Complex Data=False"
CHAPTER 2 n DATA IN, DATA OUT 46
This code is much more readable and there is less danger of breaking our code if we ever
need to point to another Access data source.
Pages:
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74