freestylpola
07-15-2014, 09:28 AM
I know nothing about Visual Basic but am trying to figure out the basic template to pull tables from SQL server. If someone could provide the basic template and just highlight where to insert by database and table information I would appreciate it!
I have tried a few other links with no luck
I have also tried this code:
ActiveWorkbook.VBProject.References.AddFromGuid "{B691E011-1797-432E-907A-4D8C69339129}", 6, 0
Sub QueryDB()
Dim dbName As ADODB.Connection
Dim dbResults As ADODB.Recordset
Set dbName = openDBConn("NAPSIKCW01", "asys_v_datadictionary")
Set dbResults = dbName.Execute("SELECT * FROM NAPSIKCW01")
While Not dbResults.EOF
'Do Something'
dbResults.MoveNext
Wend
End Sub
Function openDBConn(dataSource As String, table As String) As ADODB.Connection
Dim newDBConn As ADODB.Connection
Set newDBConn = New ADODB.Connection
newDBConn.CommandTimeout = 60
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=" & dataSource & ";INITIAL CATALOG=" & table & ";INTEGRATED SECURITY=SSPI"
newDBConn.Open strConn
Set openDBConn = newDBConn
End Function
I have tried a few other links with no luck
I have also tried this code:
ActiveWorkbook.VBProject.References.AddFromGuid "{B691E011-1797-432E-907A-4D8C69339129}", 6, 0
Sub QueryDB()
Dim dbName As ADODB.Connection
Dim dbResults As ADODB.Recordset
Set dbName = openDBConn("NAPSIKCW01", "asys_v_datadictionary")
Set dbResults = dbName.Execute("SELECT * FROM NAPSIKCW01")
While Not dbResults.EOF
'Do Something'
dbResults.MoveNext
Wend
End Sub
Function openDBConn(dataSource As String, table As String) As ADODB.Connection
Dim newDBConn As ADODB.Connection
Set newDBConn = New ADODB.Connection
newDBConn.CommandTimeout = 60
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=" & dataSource & ";INITIAL CATALOG=" & table & ";INTEGRATED SECURITY=SSPI"
newDBConn.Open strConn
Set openDBConn = newDBConn
End Function