X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Need to connect to MS SQL database in VBA coding    
post Feb 25 2008, 03:16 PM

Posts: 1,028
Joined: 27-July 04
From: Rochester NY USA

Most of my work has been in Access.
Working on a project where Excel is the better solution for the end user.
Requires VBA coding to crunch some values. (Already have number crunching code in an Access report).
h. Oh! Just discovered ... Access has the database connection defined when I linked the SQL tables. I don't have that luxury with Excel - I must define the connection myself. (I think I'm saying this correctly. Of course, you wizards already know this.)
What do I need to do to create the connection?
Hopefully I'm providing sufficient information:
The ODBC System Data Source is "SQLBase300".
I've been using DAO for my Access work (please don't confuse me with ADO)
SQL tables that I need to link to are: ORDERS, OPERATIONS, MACHINES, and ROUTING_SEQ
I've used SQL.REQUEST, and the ODBC connection parameters, successfully to retrieve data elements from the SQL db. Haven't had to do this in VBA though.)
(FYI - SQL database is 3rd party ERP software's, not mine)
THere is what I ported from my Access coding:

Dim db As DAO.Database
Dim rsOps As DAO.Recordset
Dim rsOrders As DAO.Recordset

Set db = CurrentDB
Help. :-)
The good news is most work requests seem to force me to take my skills to a new level.
Go to the top of the page
post Feb 25 2008, 06:49 PM

UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)

Confusion or not - IMO ADO is the way to go in this scenario.
The up side - it'll be another string to your bow.
Have you done class modules yet?
Depending on the settings you use to establish your linked tables - you can easily establish an ADO connection too.
Dim objADO As New clsADOConnection
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    With objADO
        .ProviderType = ddSQLOLEODBC
        .DSN = "SQLBase300"
        .Trusted = True
        Set cnn = .Connection
    End With
    Set rst = New ADODB.Recordset
    With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        Set .ActiveConnection = cnn
        .Open "SELECT * FROM TableName"
        'Do whatever with the recordset - ADO recordsets offer several similar methods to DAO ones
    End With
    Set rst = Nothing
    Set cnn = Nothing
    Set objADO = Nothing

You can use the class module (attached) to create your clsADOConnection class. Just copy and paste it into a new class module - save it as "clsADOConnection"
Attached File(s)
Attached File  1598010_2.zip ( 2.09K )Number of downloads: 18
Go to the top of the page
post Feb 26 2008, 10:38 AM

Posts: 1,028
Joined: 27-July 04
From: Rochester NY USA

Thanks for the reply.

Help me understand what you are tossing my way...

What advantages does ADO have over DAO? Most of my work is creating reports and utilities (i.e. print Shipping Labels) that ReadOnly our ERP SQL database. (3rd party ERP). DAO works fine. I've just learned DAO commands to work with some (Access) temp files I created for my latest project - INSERT records and later updated some values.

Where you indicated the recordset methods, what would be the equivalents for (these are what I use now):

Haven't done class modules.
What is the purpose of setting up a class module for an ADO connection?
When would I use it (corollary to previous question)?
How do I use it? (I picture the coding you sent sitting in a module, what statements, and where, would use this code?

Upon cursory review, it appears:
I don't need to provide an ID & Password? Correct?

I'm on a tight deadline for this project, so, I'm skimping on researching answers before asking.

Edited by: edaroc on Tue Feb 26 10:44:28 EST 2008.
Go to the top of the page
post Feb 26 2008, 11:32 AM

UtterAccess Editor
Posts: 16,086
Joined: 27-June 06
From: England (North East / South Yorks)

undamentally - in my opinion - ADO is better for accessing server data through code.
(I honestly don't think you'll get many people disagreeing with that - if they do then I'm fully willing to ignore them 'cos they're wrong and stand my ground forever :evil: heh heh).
OK, to be more serious - ADO offers a greater level of control over what you're passing and retreiving from the server.
DAO is excellent when working from within Access - using local, linked Jet or linked ODBC tables. But when it comes to directly accessing a server in code - ADO wins.
(Not saying that ADO.NET doesn't win by more - but that's not easily on the table in Office... not yet anyway :shrug:)
MS are pushing DAO over ADO now in Acc2007 - because they're pushing linked tables as their preferred data access methodology.
HAs I've said - via linked tables - fine and fair enough. But IMO linked tables alone aren't enough at all.
Now - consider that you're coming from Excel, you're wanting to use DAO without Access even in the picture.
OK - still possible for simple tasks (and fear not - I shall offer a DAO solution for you below) but I'd say only a very small proportion of serious development would be employing DAO for such work now.
The purpose of the class I provided?
To totally abstract the need for any deeper knowledge of building ADO connection objects. No remembering syntax and constants etc. Able to use OLEDB or ODBC. Trusted or passed credentials. Just by using the methods of the class.
You can create a connection using the class by passing the properties as required - and then see what the connectionstring actually looks like by examining the resulting .ConnectionString property rather than opening an actual connection.
However, you haven't done class modules so there's no need to explain nitty gritty.
(If you're curious about using classes do as I described... even just in Access to make it simpler. Create a new class module - paste the above contents into it - save it named as directed - and try the example code - you'll see how classes then offer re-usable objects).
Although we're not going down this road - it might be useful for future readers, for the equivalent methods you mention:
.Movenext => .Movenext
.movefirst => .Movefirst
.movelast => .Movelast
.recordcount => .Recordcount
.close => .Close
.findfirst => .Find (limited to single field search - use Filter otherwise... which is better than DAO's filter)
db.openrecordset => .Execute / .Open (ADO's Object model allows various ways of opening a recordset)
.edit => {ADO recordsets have no edit method - editing is implicit)
.fields => .Fields
.update => .Update
More alike than not alike I'm sure you can see.
As for what you need to connect from Excel - well that depends upon what you use when connecting from Access.
Do you have to supply a username and password at any stage? Or when linking tables, do you just select the pre-existing DSN?
Either way - you can begin with the following DAO code.
Function fDAOServerRecordset()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strConnect As String
    'With integrated security
    strConnect = "ODBC;DSN=SQLBase300;Trusted_Connection=Yes;"
    ' ** Alternatively with server Security **
    'strConnect = "ODBC;DSN=SQLBase300;UID=Username;PW=Password;"
    'Open the database and recordset
    Set db = DBEngine.OpenDatabase("Database Name", dbDriverComplete, False, strConnect)
    Set rst = db.OpenRecordset("SELECT * FROM TableName", dbOpenSnapshot)
    With rst
        'Do whatever
        If .RecordCount > 0 Then
        End If
        Debug.Print "ID " & rst.Fields(0).Value & " on record " & .RecordCount
    End With
    Set rst = Nothing
    Set db = Nothing
End Function
The "tight deadline" answer.
Go to the top of the page
post Feb 26 2008, 03:55 PM

Posts: 1,028
Joined: 27-July 04
From: Rochester NY USA

Thanks for taking the time and effort for the DAO solution AND further explaining ADO and classes.
'll use your code to keep my project moving.
Then, next week, will review and study to understand what's going on "under the hood".
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 05:41 AM