PDA

View Full Version : How To Create Macro To Pull Tables From SQL Server



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

westconn1
07-15-2014, 02:10 PM
do you have the ADO help files?

what results are you getting now?
do you get an open recordset from the table?
does you connection require username and password?
i would have expected your query to require table name rather than database name

freestylpola
07-15-2014, 02:44 PM
I don't know how to close the thread but I have the code I need now. Thank you for your reply!