Convert VB to VBA

How would this procedure be converted to VBA?

Code:

---

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" _
    (ByVal hwndParent As Long, ByVal fRequest As Long, _
    ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
    Public DB As ADODB.Connection
    Public connString As String
 
Public Sub Connect()
    Call Create
   
    connString = "DSN=VRRS;"
    Set DB = New ADODB.Connection
    DB.Open connString
End Sub
 
Private Function CreateAccessDSN(DSNName As String, DatabaseFullPath As String) As Boolean
    Dim sAttributes As String
    sAttributes = "DSN=" & DSNName & Chr(0)
    sAttributes = sAttributes & "DBQ=" & DatabaseFullPath & Chr(0)
    CreateAccessDSN = CreateDSN("Microsoft Access Driver (*.mdb)", sAttributes)
End Function
 
Private Function CreateDSN(Driver As String, Attributes As String) As Boolean
    CreateDSN = SQLConfigDataSource(0&, 1, Driver, Attributes)
End Function
 
Private Sub Create()
    Dim blnRetVal As Boolean
    blnRetVal = CreateAccessDSN("VRRS", "C:\VRRS\VRRS.mdb")
End Sub

---

What I am trying to do is use VBA to set up a DSN on my PC

Jo

If you use exactly that code as a macro on an Excel sheet (ie, VBA) ,,,
.. does it work?
.. does it crash? If so, at what line?

Spoo

I could be totally wrong, but it seems to me this was creating an ACCESS DSN, and I am needing a SQL Server DSN. However, to answer your questions, the Compile errors I get is:
Compile Error:
User-Defined type not defined
and these are the 2 lines that produces that error:

Code:

---

    Public DB As ADODB.Connection
    Set DB = New ADODB.Connection

---

add a reference to ADO

Quote:

---

Originally Posted by westconn1

add a reference to ADO

---

I added a reference to Microsoft ADO Ext. 6.0 for DDL and Security maybe that's the wrong one?

EDIT -- and it is Excel 2007

i guess i would be using ADO ver 2.8 library, but get confused with the different versions used for later versions of windows

Correct me if I am wrong, but I am looking for the Object Library right?

If that statement is true, I do not have a ADO object library available as a reference...I only have the DAO
Microsoft DAO 3.6 Object Library

Jo

DAO is an older Access feature, but still might work.
I still used DAO for VB6 apps (. :blush: .) ,, but have not tried it in VBA.

Spoo

How would the code be converted from ADO to DAO?

Jo

I'm not familiar with a DSN, so I can't help you there.

If you are only trying use Excel to open and read from Access, then maybe
I might be able to help. I'll await your reply.

Spoo

I am wanting Excel to create a DSN for SQL Server.

Quote:

---

add a reference to ADO

---

microsoft activex data objects, any system from win 98 should have available, maybe different in w7 or w8

afaik you do not have to create a DSN, just use the correct connection string, check out connectionstrings.com
the code to create a DSN should work when you have a reference to ADO

So if I just create a connection within the workbook, I will not have to create a system DSN to allow the users to be able to refresh the queries in the workbook?

Jo

What is a system DSN?

To just read an Access mdb, perhaps something like this

Code:

---

Sub ReadMyAccess()
    '
    Dim DB1 As Database
    Dim RS1 As Recordset
    '
    mymdb = "c:\access\SomeMDB.mdb"
    myrs = "SomeRS"
    '
    Set DB1 = OpenDatabase(mymdb)
    Set RS1 = DB1.OpenRecordset(myrs)
    '
    v1 = RS1.RecordCount
    RS1.MoveFirst
    For ii = 1 To 6
        v2 = RS1("date")
        v3 = RS1("day")
        RS1.MoveNext
    Next ii
End Sub

---

I added these Tools>References

,, Microsoft Access 11.0 Object Library
,, Microsoft DAO 3.6 Object Library

Spoo

Quote:

---

I added these Tools>References

,, Microsoft Access 11.0 Object Library
,, Microsoft DAO 3.6 Object Library

---

the first would require access to be installed and DAO should be avoided with any other database than access, even with access it is better to use ADO now

Quote:

---

the first would require access to be installed and DAO should be avoided with any other database than access, even with access it is better to use ADO now

---

Agreed, but OP indicated that an ADO reference was not an available reference.
So, are you suggesting dropping the first one and just keeping the second?

Quote:

---

So, are you suggesting dropping the first one and just keeping the second?

---

no, the first may not be available if access is not installed and he indicated it was not an access database, but sql server

i suggest he finds why ADO is not available, if he is using w7 or w8 x64, it may well be a different driver