VBA to query the data from sql server and save the recordset in a table/view

[Mark_]

I feel make table query in the VBA is easy to use rather than record set

Are you using this approach because your table's definition changes on the server from time to time and you want to make sure your using the current table structure?

 

[penchalas]

Hi. Not sure how you tried it, but did it look anything close to this?

Public Sub CompleteDatabaseExample() 

Dim dbMain as New ADODB.Connection' Declaring the Connection 
Dim SQL as String' Declaring a simple variable

dbMain.Open = "Provider=sqloledb;Server=LDNPCM05421V05A,10501;Da   tabase=TMS;" & _ "uid=INTRANET\sysemtmslive;pwd=test123;Trusted_Connection=no"

strSQL = "SELECT * INTO SanTest FROM pc_test"

Dim adoCmd As New ADODB.Command
adoCmd.ActiveConnection = dbMain
adoCmd.CommandText = strSQL
adoCmd.Execute

 End Sub

Yes. I did the same, but when i clicked the button, nothing is shown.
And when i click it again, it says "Table already exists". But no where i can see the table

 

[penchalas]

Are you using this approach because your table's definition changes on the server from time to time and you want to make sure your using the current table structure?

Yes, i want to use the latest table definition everytime

 

[theDBguy]

Yes. I did the same, but when i clicked the button, nothing is shown.
And when i click it again, it says "Table already exists". But no where i can see the table

Hi. Did you refresh your Nav Pane?

 

[penchalas]

Hi. Did you refresh your Nav Pane?

Just checked that the table is getting created in SQL server instead of MS Access

Below is my code


Private Sub btnGetData_Click()
Dim dbMain As New ADODB.Connection ' Declaring the Connection
Dim rs As New ADODB.Recordset ' Declaring the Recordset
Dim strSQL As String ' Declaring a simple variable

dbMain.Open "Provider=sqloledb;Server=test;Database=DemoTest;uid=u1234;pwd=p1234;Trusted_Connection=no"


strSQL = "SELECT policynumber into SanTest FROM Test123"
Dim adoCmd As New ADODB.Command
adoCmd.ActiveConnection = dbMain
adoCmd.CommandText = strSQL
adoCmd.Execute

End Sub

 

[theDBguy]

Just checked that the table is getting created in SQL server instead of MS Access

Oh, I see. Let me see if I could find a different resource.

 

[penchalas]

Oh, I see. Let me see if I could find a different resource.

Yes please. This is a bit urgent, so please do the needful

 

[theDBguy]

Yes please. This is a bit urgent, so please do the needful

Hi. Check out the last post in this other thread. It uses OPENROWSET(). Hope it helps...

 

[penchalas]

Hi. Check out the last post in this other thread. It uses OPENROWSET(). Hope it helps...

Thanks much!
It worked

 

[sportsguy]

instead of creating a new table via vba, its easier to create a new table by copying and renaming an existing table with the desired or identical server table structure. This approach assumes that the SQLSERVER table structure remains constant. Then just run an append query, column for column, excluding autoincrement fields.

sportsguy