Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Pulling a name from an Access database and putting it in Excel

  Asked By: Dai    Date: Jan 28    Category: MS Office    Views: 903
  

I'm trying to write my first ever macro with SQL, to read data from an
Access 2007 database and put it in a spreadsheet.

The database's full path and name is stored in a named cell in the
spreadsheet, [Databasepath]. The table which has this particular
piece of info is tblClientCodes, and the piece of data I'm currently
grabbing is in the "Client surname" column of tblClientCodes. The
client is being uniquely identified by "ClientID" which is equal to a
value in the named cell [ClientID} in the spreadsheet.

First I need to get the basic SQL thing out of the way, then once I
can get this figured out I'll modify it into a much more elaborate
thing which loops through a bunch of named cells and systematically
pulls pieces of info one after the other to populate a hundred or so
named cells in my spreadsheet with database data.

I've done quite a lot of Excel VBA stuff but am a complete novice with
integrating Access and Excel. This macro is based closely on code
from "Microsoft Excel and Access Integration With Microsoft Office
2007" by Michael Alexandeer and Geoffrey Clark, so I'm not sure how to
debug it other than checking that I've definitely not done any typos!

Anyway, the problem I get when I run it is a popup "Run-time error
'-2147217904 (80040e10)': No value given for one or more required
parameters." and the yellow highlighted line in debug is
MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

There is probably something really basic wrong with it, but I'm
stumped. Can anyone see any obvious reasons why it would not be working?

Sub Grabname()

'Step 1: Declare your variables
Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String

'Step 2: Declare your connection string
MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= " & [Databasepath].Value

'Step 3: Build your SQL statement
MySQL = "SELECT tblClientCodes.[Client surname] FROM tblClientCodes " & _
"WHERE (((tblClientCodes.[ClientID])= " & [ClientID].Value & "));"

'Step 4: Instantiate and specify your recordset
Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly
'Step 5: Copy the recordset to Excel

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Sydney Thompson     Answered On: Jan 28

Here's an idea I did not see in your post. Did you set a reference
to the ADO library using the reference command in the VBE?

Select Tools > References. Find the Microsoft ActiveX Data Objects
with the highest version number. I use Microsoft ActiveX Data
Objects 2.8 Library.

 
Answer #2    Answered By: Kim Cruz     Answered On: Jan 28

Yes, I did that already, so that's not it.

 
Answer #3    Answered By: Adelbert Fischer     Answered On: Jan 28

Always display your SQL statement somewhere before you execute it. It is
often almost impossible to find a simple problem just by looking at the code
that creates the SQL statement. You can put it into a cell so that you can
see it, or put it in the status bar, or even just put it in a MsgBox. But
always do it, and always include that statement in messages when you are
asking questions about databases.

Seeing your connect string is also generated, you need to do the same for
it.

Is the ClientID a number? If it isn't, you need to put quotes around it.
If it is a number, then you need to show us the complete SQL statement and
the connect string.

 
Answer #4    Answered By: Bian Nguyen     Answered On: Jan 28

Yes, inserting a couple of single quotes into the generated string
around the ClientID did the trick.

Many thanks to all for your assistance.

 
Answer #5    Answered By: Daniel Jones     Answered On: Jan 28

Hope you don't mind me asking...
What was your final connection string please??

 
Answer #6    Answered By: Mercedes Andrews     Answered On: Jan 28

As someone else noted, you need to reference the ADO object library
when using ADO, the code is as below.

I've already created a looping version of this which can read  hundreds
of bits of data  in one hit, my next challenge will be creating a sub
that sends the data the other way, to save the data back into Access
when I'm done modifying it, or create a new entry when it is data
typed in from scratch. If anyone wants to preempt my question and
give me a few lines of SQL and ADO code giving the appropriate
connection strings etc, I will be forever in your debt. :)

Here is the fully working macro.

Someone did point out to me that the macro  might have problems with
surnames like O'Donnel and the like because the ' will mangle the
string, but he said a workaround is to create a global varable called
SingleQuote with the value ' and use that in place of the '.

Private Sub ReadFromAccess_Click()

'Step 1: Declare your variables

Dim MyConnect As String
Dim MyRecordset As ADODB.Recordset
Dim MySQL As String

'Step 2: Declare your connection string

MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= " & [Databasepath].Value

'Step 3: Build your SQL statement

MySQL = "SELECT tblClientCodes.[Client surname] FROM tblClientCodes
WHERE (((tblClientCodes.[ClientID])= '" & [ClientIdentifier].Value & "'))"

'Step 4: Instantiate and specify your recordset

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockReadOnly

'Step 5: Copy the recordset to Excel

[Client1Surname].CopyFromRecordset MyRecordset

End Sub

 
Answer #7    Answered By: Fuzairah Neeman     Answered On: Jan 28

Actually I figured it out for myself. There is no step 5 any more,
step 3 and 4 as below will update an existing record.

'Step 3: Build your SQL statement

MySQL = "UPDATE tblClientCodes Set tblClientCodes.[Client surname] =
'" & [Client1Surname].Value & "' WHERE (((tblClientCodes.[ClientID])=
'" & [ClientIdentifier].Value & "'))"


'Step 4: Instantiate and specify your recordset

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockOptimistic


That won't actually do anything if it is a new record you are
creating. To add a new record you need to use INSERT instead of
UPDATE. Like so:

'Step 3: Build your SQL statement

MySQL = "INSERT INTO tblClientCodes (ClientID, [Client surname])
VALUES ('" & [ClientIdentifier].Value & "','" & [Client1Surname].Value
& "')"

'WHERE (((tblClientCodes.[ClientID])= '" & [ClientIdentifier].Value &
"'))"



'Step 4: Instantiate and specify your recordset

Set MyRecordset = New ADODB.Recordset

MyRecordset.Open MySQL, MyConnect, adOpenStatic, adLockOptimistic





Hope this helps. Next step I guess would be setting up a test to see
if the record exists, so it knows whether to use the UPDATE SQL or the
INSERT SQL.

But it appears that reading and writing from a database  to Excel is
actually pretty easy to do.

 




Tagged: