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

Mark_

Longboard on the internet
Local time
Today, 05:27
Joined
Sep 12, 2017
Messages
2,111
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

Registered User.
Local time
Today, 17:57
Joined
May 14, 2019
Messages
26
Hi. Not sure how you tried it, but did it look anything close to this?
Code:
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

Registered User.
Local time
Today, 17:57
Joined
May 14, 2019
Messages
26
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

I’m here to help
Staff member
Local time
Today, 05:27
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 17:57
Joined
May 14, 2019
Messages
26
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
 
Last edited:

sportsguy

Finance wiz, Access hack
Local time
Today, 08:27
Joined
Dec 28, 2004
Messages
358
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
 

Users who are viewing this thread

Top Bottom