Now we just need to change the Source property of the ListObjects.Add method to refer
to the connection string in place of the array:
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=sConnString, ??
Destination:=Range("$A$1")).QueryTable
With a couple of quick and easy changes, we??™ve made the Macro Recorder??“generated code
much easier to read and modify. Let??™s import the same data onto Sheet2 in the workbook:
1. Navigate to Sheet2 in the workbook.
2. Run the GetAccessData2 macro.
Oops, we??™ve generated an error (see Figure 2-1).
Figure 2-1.Macro code generates error
Why should there be an error? Excel generated this code itself (with the exception of your
addition of a string variable). Click the Debug button, and the VBE should show us the errant
line of code (see Figure 2-2).
Figure 2-2. DisplayName property fires error
The ListObject.DisplayName property creates a named range on the worksheet. Even
though we??™re working on Sheet2, a range named Table_Northwind_2007a.accdb already exists
in this workbook. Easy enough to fix:
1. Click Debug.
2. Remove or change the a before the file extension, or simply choose another name
entirely.
3. Press F5 to continue running the code.
CHAPTER 2 n DATA IN, DATA OUT 47
There is still a lot of code stored on our sConnString variable. Many of the Jet database
property values default to False, since we did a simple import of data. We can remove them
from our connection string and leave just the essential information required to access our
Northwind database.
Pages:
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75