Sign in
 
HomeLibraryLearnDownloadsRepositoryCommunityForumsBlog
Script Center >
> 
> 
Trying to dump Citrix farm data into Access database - Error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

Answered Trying to dump Citrix farm data into Access database - Error [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

  • Wednesday, February 13, 2013 8:51 AM
     
     
    Sign In to Vote
    0

    Hi,

    We have a Citrix Xenapp 5 farm running on Windows 2003 R2

    I am trying to dump Citrix data farm information into a Access Database (.mdb). When i try to run the script with cscript i get  

    CountSessions.vbs(23, 1)  [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified 

    Does this error mean that i have to install the MS Access Database Engine 2010 Redistributalbe? Installing this database engine, does this affect the correct funtioning of other software installed on the server or does my script contain any errors?

    The Access database  name is 2000_scbctxffm02.mdb and is located in the c:\temp folder

    I have created a System DSN OBDC connection, without user and password that points to this database.

    Please help. Thanks in advance

    VBS script:

    Option Explicit

    Const cMetaFrameWinFarmObject = 1
    Dim theWinFarm, theFarm, asession, strDate, strTime, strNumeroUsuarios, strVersion
    Dim strAgno, strMes, strDia, strHora, strMinuto

    Dim Connect, selectSQL, RecSet
    Set Connect = CreateObject ("ADODB.Connection")
    Connect.Open "DSN=SCBCTXFFMM02;UID=;PWD="

    Call CreateStrFecha

    set theFarm = CreateObject("MetaFrameCOM.MetaFrameFarm")
    theFarm.Initialize 1
    set theWinFarm = theFarm.WinFarmObject2

    for each aSession in theFarm.sessions
     if asession.sessionid > 0 and asession.sessionid < 65000  and (instr(asession.sessionName,"ICA")>0) Then
      Select Case asession.clientbuild
       Case 24737
        strVersion = "8.00"
       Case 29670
        strVersion = "8.10"
       Case 32649
        strVersion = "9.00"
       Case 36280
        strVersion = "9.10"
       Case 39151
        strVersion = "9.15"
       Case 44376
        strVersion = "9.20"
       Case 50211
        strVersion = "9.23"
       Case 52110
        strVersion = "10.00"
      End Select

      selectSQL = "Insert INTO Sesiones (Fecha,Hora,Servername,AccountName,BrowserName,ClientName,VitualIP,ClientBuildNumber,ClientAddress) VALUES ('" & strDate & "','" & strTime & "','" & asession.username & "','" & asession.clientname & "','" & asession.clientaddress & "','" & strVersion & "','" & asession.appname & "','" & asession.servername & "')"
      Set RecSet = Connect.Execute (selectSQL)
     End If
    Next

    MsgBox "no hay mas sesiones"


    Sub InsertaLicencias
     selectSQL = "SELECT COUNT(DISTINCT Usuario) FROM Sesiones Where Fecha ='" & strDate & "' and Hora='" & strTime & "'"
     Set RecSet = Connect.Execute (selectSQL)
     MsgBox RecSet
     selectSQL = "Insert INTO Licencias (Fecha, Hora, Licencias) VALUES ('" & strDate & "','" & strTime & "'," & strNumeroUsuarios & ")"
     Set RecSet = Connect.Execute (selectSQL)
     Set RecSet = Nothing
     Set Connect = Nothing
    End Sub

    Sub  CreateStrFecha
     Dim strHora
     strAgno = DatePart("yyyy", Now) ' Year
     strMes = DatePart("m", Now) ' Month
     If cint(strMes) < 10 Then
      strMes = "0" & strMes
     End If

     

All Replies

  • Wednesday, February 13, 2013 3:49 PM
     
     Answered
    Sign In to Vote
    0

    Look in repository for examples of how to conect to an access database.  ADODB does not use ODBC.  It uses a connection string.  DSNless connections are necessary or you will need to do other things.

    YOu need to use the Jet x.x driver for access as MSAccess does not have an ODBC driver.

    ¯\_(ツ)_/¯

     
  • Thursday, February 14, 2013 10:39 AM
     
     
    Sign In to Vote
    0

    Hi, thanks for you answer but i think if you download MS Access Database Engine 20010 Redistirubalble should fix de OBDC driver issue. I installed it still it does not work

    I used this engine in Powershell and it works ok but i have no idea how to do this in vbs.

    Regards

     
  • Thursday, February 14, 2013 1:51 PM
     
     Answered
    Sign In to Vote
    0

    Hi, thanks for you answer but i think if you download MS Access Database Engine 20010 Redistirubalble should fix de OBDC driver issue. I installed it still it does not work

    I used this engine in Powershell and it works ok but i have no idea how to do this in vbs.

    Regards

    Microsoft access drivers are installed on every system by default.  ODBC cannot be used with Access,  You must use ADO semantics.  You are using a DSN but incorrectly. With ADO is is better to use a DSN-less connection method.

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\scripts\test.mdb;Persist Security Info=False

    This will connect if you have the 64 bit drivers on a 64 bit system or it will connect in 32 bit mode if you are running a 32 bit script.

    On Windows 7 and later the 64 bit drivers need to be installed as only the 32 bit drivers are installed.

    Her are the 64 bit drivers.:

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    ¯\_(ツ)_/¯

     
  • Thursday, February 14, 2013 2:00 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Here is an example that is tested. Just put in the location of you file and change the table name to test.

    Set conn=CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\scripts\test.mdb;Persist Security Info=False"
Set rs=conn.Execute("select * from table1")
msgbox "done no errors"

    ¯\_(ツ)_/¯