0

SQL queries to extract data from METEAM Database using VBA

Hi Everyone

Has anyone figured out how to retrieve data from the METTEAM database using ADODB and VBA. We want to import some METTEAM data into a Excel spreadsheet.

5 comments

Date Votes
0
Avatar
Josh Cole

Hello Mauray,

MET/TEAM is backed by a standard SQL Server instance (typically SQL Server 2008 R2). If you need help with the actual VBA code, the following URL will take you to a Microsoft resource with more information on how to access an SQL Server. 

http://support.microsoft.com/default.aspx?scid=kb;en-us;306125&sd=tech

The only thing that will be different is the connection string. This is what tells Excel where the database you are trying to access is located. 

In the example code provided by the link above, they use the following terms: 

Data Source: This is the name of the computer which is hosting the SQL Server.
Initial Catalog: This is the database name. It is defaulted to 'METTEAM'.

A typical connection string (in it's entirety) might look like this:
Data Source=[Network Computer Name]\SQLEXPRESS;Initial Catalog=metteam;Integrated Security=SSPI

 

I hope this helps. If you have any more questions, I would be more than happy to help.

Sincerely,

Joshua Cole
Software Engineer
0
Avatar
Mauray Ganter

Hi Joshua

Thanks for the useful info. What about if we need to provide a username and password to access the database?

How is that implemented in the VBA code?

Regards

Mauray

0
Avatar
Mankani, Soumya

Hello Mauray,

I use VB6, I think VBA and VB6 are quite similar.

The connection string I am using are given below: 

For Windows Authentication:

ConnectionString = "Provider=sqloledb;Data Source={Server_Name};Initial Catalog= {metteam};Integrated Security=SSPI;User ID={User_Name};Password={Password};"

 

For SQL Authentication:

ConnectionString = "Provider=sqloledb;Data Source={Server_Name};Initial Catalog= {metteam};User ID={User_Name};Password={Password};"

Both strings work perfectly fine for me. Hope this helps.

Thanks,

SM

0
Avatar
Mauray Ganter

Hi SM

Thanks for the reply. I still have a minor issue to resolve to make this work. My VBA code so far is;

Sub GetDatabaseData(sID As String)
'================================================================================================================================
'  Field names required from the CalLab Data base are as follows
'  cDescription, cManufacturer, cModelNumber, cSerialNumber, cID, ItemCertificate, tMaintDate, tNextMaintDate, cFacilityNumber, cFacilityName
'  cID = Item Asset Number [either a TR generated ID or customers ID] TR generated ID is the customer Number.
'  E.g. CO377 followed by a 3 digit incrementing number. So TR Asset No looks like CO377001
'  tMaintDate = Last Calibration date
'  tNextMaintDate = Calibration next due date
'  nFacilityUID = Owner of Asset
'  cFacilityNumber = Name of Facility where asset resides
'  sID = Workorder Number 10 digits
 
'  Object Descriptions
'---------------------------------------------------------------------------------------
'   Connection      Refers to the connection to the data source.
'   Recordset       Refers to the data extracted.
'   Command         Refers to a stored procedure or SQL statements that need to be executed.
'================================================================================================================================
Dim JobDetRange As Range
Dim SQLStr As String
Set JobDetRange = Worksheets("Job_Details_Master").Range("Job_Details_Master_Item")
' Create a connection object.
Dim cnPubs As ADODB.Connection
Set cnPubs = New ADODB.Connection
' Create a recordset object.
Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset
' Provide the connection string.
Dim strConn As String
'Use the SQL Server OLE DB Provider.
 strConn = "PROVIDER=SQLOLEDB;"
'-----------------------------------------------------------------
'Connect to the Pubs database on the local server.
 strConn = strConn & "DATA SOURCE=TRCAL\sqlexpress;INITIAL CATALOG=metteam;"
'-----------------------------------------------------------------
 'Place required SQL query parameters in SQLStr string.
    SQLStr = "SELECT DISTINCT cDescription, cManufacturer, cModelNumber, cSerialNumber, cID, "
    SQLStr = SQLStr & " tMaintDate, tNextMaintDate, cFacilityNumber, cFacilityName FROM vw_CallSheets WHERE cCallSheetNumber = '" & sID & "'"
'Use an integrated login.
SQLStr = strConn & " INTEGRATED SECURITY=sspi;"
'Now open the connection.
cnPubs.OPEN SQLStr
With rsPubs
    ' Assign the Connection object.
    .ActiveConnection = cnPubs
    ' Extract the required records.
    .OPEN "SELECT * FROM vw_CallSheets"
    ' Copy the records into JobDetRange
    JobDetRange.CopyFromRecordset rsPubs
'----------------------------------------
    ' Tidy up
    .CLOSE
End With
'-------------------------------------------------------------
cnPubs.CLOSE
Set rsPubs = Nothing
Set cnPubs = Nothing
'Check if any data was imported by checking IUT Description field
  If Worksheets("Job_Details_Master").Range("A37") = "" Then
    MsgBox ("This Job Item ID does not have any saved data! Please try again.")
    Worksheets("Job_Details_Master").Range("CalDataFileName") = ""
  End If
  Application.ScreenUpdating = True
End Sub
 
When i run the Subroutine, i get a bucketload of numeric data splattered all over my Excel worksheet instread of what i am wanting in the worksheet RangeName i want to populate. Any help would be apprecieated.
 
Regards
Mauray
0
Avatar
francois lamarche

How does one figure out {metteam} ?

 

Please sign in to leave a comment.