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

[penchalas]

Hi All,


I have gone through few threads but nowhere i found the proper solution. Below is my requirement


1. Data is located in SQL server
2. From MS Access , i need to connect to sql server using sql server authentication and then query the tables

3. After that i need to create tables/views from the recordset through VBA
4. Am using form interface, button control for this




Could anyone please help me how to get through VBA?


I have tried option of connecting sql server through ODBC, but that is not what am looking for

 

[theDBguy]

Hi. I’m not sure I fully understand. You said you’re stuck with point #3, which means you already passed #2, but you also said you don’t want to use ODBC. If so, how did you connect to SQL Server?

 

[isladogs]

I'm also unclear what you are asking.
You need to create tables and possibly views in SQL Server
Then you should link those tables /views in Access using either ODBC or DSN-less connection strings.
Once you have done so, both the linked tables and views will appear in the navigation pane and can be used just like local tables

 

[penchalas]

Sorry for the confusion.

I want to achieve this through VBA code:

1. Connect to sql server database via ADO
2. Run the query and create a new access table with the query result set

Basically, i want to create tables in access with the query result set. All this has to be done through VBA code

 

[theDBguy]

Sorry for the confusion.

I want to achieve this through VBA code:

1. Connect to sql server database via ADO
2. Run the query and create a new access table with the query result set

Basically, i want to create tables in access with the query result set. All this has to be done through VBA code

Hi. Thanks for trying to clarify but I am still a little fuzzy on the details. Like I said, you implied having done the connection already. So, if you were using ADO to do it (and not ODBC), can you please show us the code you used to accomplish it? Thanks. I just need to make sure I'm clear before advising any action. It needs to be in line with what you're already doing. Cheers!

 

[penchalas]

Below is the VBA code:

Public Sub CompleteDatabaseExample()
Dim dbMain as New ADODB.Connection' Declaring the Connection
Dim rsCustomer as New ADODB.Recordset' Declaring the Recordset
Dim SQL as String' Declaring a simple variable
dbMain.Open = "Provider=sqloledb;Server=LDNPCM05421V05A,10501;Database=TMS;" & _ "uid=INTRANET\sysemtmslive;pwd=test123;Trusted_Connection=no"

End Sub


Please let me know if this is correct

 

[theDBguy]

Below is the VBA code:

Public Sub CompleteDatabaseExample()
Dim dbMain as New ADODB.Connection' Declaring the Connection
Dim rsCustomer as New ADODB.Recordset' Declaring the Recordset
Dim SQL as String' Declaring a simple variable
dbMain.Open = "Provider=sqloledb;Server=LDNPCM05421V05A,10501;Database=TMS;" & _ "uid=INTRANET\sysemtmslive;pwd=test123;Trusted_Connection=no"

End Sub


Please let me know if this is correct

Okay, thanks. So, I guess when you said you didn't want to use ODBC, it's because you are using OLEDB, correct? I wanted to be sure about your approach because most advice I heard recommends ODBC over OLEDB (if memory serves). In any case, if you are using an ADO recordset to grab the data from the server and simply want to create a local table with those data, then you should be able to "dump" the data from the ADO recordset to a local table. Do you already have a local table in place or do you have to create a new one each time? If you have one, you can empty it out or simply add to it. If you don't have one, then you could create one using code too. However, since you are using a recordset object, you will have to loop through the records and add each item to the local table one row at a time. It might potentially be quicker to transfer the recordset data to Excel and then import the Excel data into a local table (it would require more code though). But, I think, the simplest approach is to skip the recordset object and simply execute a Make-Table query using the same connection you're using for your recordset. Hope it helps...

 

[penchalas]

So, I guess when you said you didn't want to use ODBC, it's because you are using OLEDB, correct?-- Which ever is convenient, am ok with either
(Am using SQL server authentication in connection string)

And yes, every time i want to create new table instead of appending or inserting into existing one.

I want to assign this vba code to button control, so that on click the table is created everytime

 

[theDBguy]

So, I guess when you said you didn't want to use ODBC, it's because you are using OLEDB, correct?-- Which ever is convenient, am ok with either
(Am using SQL server authentication in connection string)

And yes, every time i want to create new table instead of appending or inserting into existing one.

I want to assign this vba code to button control, so that on click the table is created everytime

Hi. Convenience is a matter of personal preference, I think. I was only curious about your statement of not wanting to use ODBC because I seem to recall reading somewhere where someone said that OLEDB is on its way out and we should use ODBC instead. I can't say if this is true or not, I'll have to look around to see who said it. Was there any particular reason why you decided to use OLEDB or why you didn't want to use ODBC?


As for using VBA, you can add code to check if the table exist and then delete it before making a new one. That is, if you're creating the table with the same name each time. Also, if you're saving the same set of data everytime you click the button, then creating a new table or simply replacing the data would not make much difference. Either way, I was saying to skip creating the recordset to do this (move data from server to local table). However, if you "must" have a recordset first, then you will have to loop through its records and append each one to the local table one row at a time.

 

[penchalas]

Nothing particular on OLEDB or ODBC. I just found in google the code snippet that's it.

Could you please send me the VBA code for achieving this: (As for using VBA, you can add code to check if the table exist and then delete it before making a new one. That is, if you're creating the table with the same name each time. Also, if you're saving the same set of data everytime you click the button, then creating a new table or simply replacing the data would not make much difference. Either way, I was saying to skip creating the recordset to do this (move data from server to local table). However, if you "must" have a recordset first, then you will have to loop through its records and append each one to the local table one row at a time.)

 

[theDBguy]

Nothing particular on OLEDB or ODBC. I just found in google the code snippet that's it.

Could you please send me the VBA code for achieving this: (As for using VBA, you can add code to check if the table exist and then delete it before making a new one. That is, if you're creating the table with the same name each time. Also, if you're saving the same set of data everytime you click the button, then creating a new table or simply replacing the data would not make much difference. Either way, I was saying to skip creating the recordset to do this (move data from server to local table). However, if you "must" have a recordset first, then you will have to loop through its records and append each one to the local table one row at a time.)

I see. Okay, here are some more code snippets (I'm not sure which approach you specifically wanted to take).


To delete a table if it exists:

On Error Resume Next
CurrentDb.TableDefs.Delete "TableName"

To execute a make-table query:

strSQL = "SELECT * INTO NewTableName FROM ServerTableName"
Set conn = New ADODB.Connection
conn.ConnectionString = "YourConnectionString"
conn.Open

conn.Execute strSQL

Hope it helps...

 

[penchalas]

Could you please send me the complete VBA code

 

[penchalas]

I just wrote this in button click , but i don't see table getting created (SanTest is the table name)


Dim dbMain As New ADODB.Connection ' Declaring the Connection
Dim rsCustomer As New ADODB.Recordset ' Declaring the Recordset
Dim strSQL As String ' Declaring a simple variable

dbMain.Open "Provider=sqloledb;Server=testgwis;Database=SQA;uid=gwuser;pwd=gwusrSIT;Trusted_Connection=no"
strSQL = "SELECT * INTO SanTest FROM pc_test"
dbMain.Execute strSQL

 

[theDBguy]

Could you please send me the complete VBA code

Hi. Unfortunately, a complete VBA solution would depend on what you have, which I don't see. I don't have your database file, I don't have access to your server, etc. How about posting the code you have, and we'll try to see if we can fix it for you. If the one you posted earlier is all you have, then you'll have to tell us which approach, from what I described earlier, would you like to take? Do you want to discard your recordset code and simply use a make-table query? Or, do you want to keep your recordset object and just loop through the records?

 

[theDBguy]

I just wrote this in button click , but i don't see table getting created (SanTest is the table name)


Dim dbMain As New ADODB.Connection ' Declaring the Connection
Dim rsCustomer As New ADODB.Recordset ' Declaring the Recordset
Dim strSQL As String ' Declaring a simple variable

dbMain.Open "Provider=sqloledb;Server=testgwis;Database=SQA;uid=gwuser;pwd=gwusrSIT;Trusted_Connection=no"
strSQL = "SELECT * INTO SanTest FROM pc_test"
dbMain.Execute strSQL

Hi. Unfortunately, that is not something I could test from here, since I don't have access to your server. Maybe someone else will see what's wrong/missing and jump in. Let's wait...

 

[penchalas]

Hi. Unfortunately, a complete VBA solution would depend on what you have, which I don't see. I don't have your database file, I don't have access to your server, etc. How about posting the code you have, and we'll try to see if we can fix it for you. If the one you posted earlier is all you have, then you'll have to tell us which approach, from what I described earlier, would you like to take? Do you want to discard your recordset code and simply use a make-table query? Or, do you want to keep your recordset object and just loop through the records?

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

 

[theDBguy]

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

Me too, but we just need to make it work. I'll keep trying while we wait...

 

[theDBguy]

Me too, but we just need to make it work. I'll keep trying while we wait...

Hi. I found another example here. See if this works for you. It uses CommandText.

 

[penchalas]

Hi. I found another example here. See if this works for you. It uses CommandText.

It did not work for me. It doesn't create a new table rather insert into the existing. Code showing many errors too


We better go with make table only!

 

[theDBguy]

It did not work for me. It doesn't create a new table rather insert into the existing. Code showing many errors too


We better go with make table only!

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