Post
Ask Question
Abey Varghese

Abey Varghese

  • NA
  • 18
  • 9.4k

Database connection from Excell

Oct 21 2013 12:45 PM

Hi I am new to Excel VBA . I want to change my VBA code to connect to SQL Databse . Here is my Old Code connection to Access.

With Sheets(SName).QueryTables.Add(Connection:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=C:\databases\waste.mdb;DefaultDir=c:\databases;Dri" _ ), Array( _ "verId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=" & Pass & ";UID=admin;" _ )), Destination:=Sheets(SName).Range("A1")) .CommandText = Array( _ "SELECT qryTotalWeight.ProfileDes, qryTotalWeight.ContainerIDString, qryTotalWeight.Weight, qryTotalWeight.OpenDate, qryTotalWeight.CloseDate" & Chr(13) & "" & Chr(10) & "FROM C:\databases\waste.qryTota" _ , "lWeight qryTotalWeight") .Name = "Query from MS Access Database" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With Application.CommandBars("External Data").Visible = False

I want to use this querry for get the data from sql database.

SELECT p.ProfileDes,c.ContainerIDString,c.weight,c.opendate,c.closedate,c.manifestid FROM tblprofile p LEFT OUTER JOIN tblcontainers c ON c.profileid = p.profileid WHERE c.containerstatusid <> 0

Need Help. Thanks


Answers (1)