Inserting data from SQL query to Access Table

[AlexTeslin]

Hi,

I have a SQL query written in Excel VB Module, which generates the data and stores in Recordset. Like this:

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open sqlString, connectionVar, adOpenKeyset, adLockPessimistic

With this I can copy the data into Excel worksheet like this:

Cells(1,1).CopyFromRecordset rst

Now, instead of copying the data from query into Excel worksheet, I would like to copy that data into another table in Access. I have already created an Access file and a table for it. I also made a connection to the file from the Excel Module as i did for SQL Server.

How can I now copy the data to the specified table? I can implement INSERT INTO query, but how can I use my data which is stored in Recordset variable rst?

Any help will be much appreciated, thank you

 

[KenHigg]

You could create a second recordset based on the table you want to append to and use the .addnew method.

???

 

[AlexTeslin]

Thanks Ken,

What i was doing is this:

INSERT INTO [myAccessTable] (col1, col2)
FROM rst1

And obviously it doesn't work because I can't pass rst1 (which is a variable for my SQL Recordset result) to INSERT query.

I have a Recordset with my Access table as well, but would you be more specific on using .addNew method please?

Thanks again

 

[KenHigg]

Something like (pseudo code):

Open recordset1
Open recordset2
Loop through recordset1
   recordset2.addnew (add new record)
   recordset2.fld1 = recordset1.fld1
   recordset2.saverecord
   recordset1.movenext
recordset1 loop
Close recordset1
Close recordset2

 

[AlexTeslin]

Thanks,

I think i have an idea now. For some reason when I am using this for SQL Server works fine:

Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
rst1.Open strSQL1, cn, adOpenKeyset, adLockPessimistic

But when I use this for Access - it dowsn't work. I have new Recordset variable, new connection and very simple Select query. I am getting "Syntax error in FROM clause". The query works fine in Access. Any ideas?

I also have no idea what i need to pass as a parameter to addNew function?

Cheers,

 

[AlexTeslin]

I am still getting "Syntax error in FROM clause"

Any ideas please?

 

[AlexTeslin]

I have managed to do it. What I am really looking is to stop using Recordsets and try to use in INSERT query such as:

INSERT INTO table1(Field1, Field2)
SELECT field1, field2 FROM [" & connectionToSQLServerVar & "].table2

I can use INSERT query from one table to another within same Access file. But how would you do this from SQL table into Access table?

Thanks

 

[AlexTeslin]

Hi guys,

I am still having trouble with it. I have tried several ways, but no luck. I am posting my code here in case anyone can help, thanks

Sub MyTestModule()
    
    Dim cn As New ADODB.Connection
    Dim cn2 As New ADODB.Connection
    Dim rst1 As ADODB.Recordset
    Dim rst2 As ADODB.Recordset
    Dim sqlVersion1 As String
    Dim sqlVersion2 As String
    Dim strSQLServer As String
    Dim strAccess As String
    
    
'    'Make a connection to SQL Server - DO NOT FORGET TO INCLUDE ERROR CLAUSE
    strSQLServer = "Provider=SQLOLEDB;Data Source=myServer\dbName;User ID=myID;Password=myPassword;Initial Catalog=dbName;"
    cn.Open strSQLServer
    
    'Make a connection to Access Database
    strAccess = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=W:\Monitoring and Reporting\" & _
        "CopyToDatabase.mdb"
    cn2.Open strAccess
        
    'VERSION - 1
    sqlVersion1 = "INSERT INTO AccessTable(Test1) " & _
        "SELECT person_ref FROM [" & strSQLServer & "].person"
        
    Set rst1 = New ADODB.Recordset
    rst1.CursorLocation = adUseClient
    rst1.Open sqlVersion1, cn2, adOpenKeyset, adLockPessimistic

    'VERSION - 2
    sqlVersion2 = "INSERT INTO OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=W:\Monitoring and Reporting\" & _
                        "CopyToDatabase.mdb') AccessTable(Test1) " & _
                "SELECT person.person_ref FROM [" & strSQLServer & "].person"
    Set rst2 = New ADODB.Recordset
    rst2.CursorLocation = adUseClient
    rst2.Open sqlVersion2, cn2, adOpenKeyset, adLockPessimistic
    
    
    'Cleanup objects
    rst1.Close
    rst2.Close
    cn.Close
    cn2.Close
    Set rst1 = Nothing
    Set rst2 = Nothing
           
    
End Sub

 

[LPurvis]

OK - a few problems.
Firstly, though it's not a problem per se, it's incorrect practice.
You're using the opening method of a recordset to execute action SQL statements (which don't return records).
i.e. Instead of
rst1.Open sqlVersion1, cn2
you should use
cn2.Execute sqlVersion1

Secondly - and this is the biggy - you're attempting to connect to external sources through Jet (fine) but using an external data source defined using an OLEDB provider. You must do so through ODBC - not OLEDB.
And in one instance you're using a connection to a SQL Server DB. T-SQL doesn't support that syntax at all! It however can use an OLEDB provider to connect to external data - using a Linked Server or the built in function OPENROWSET. But the syntax is different.

You must always bear in mind where your connection is taking you.
The syntaxes available upon a SQL box are occasionally substantially different to the SQL provided by Jet.

Finally just to make clear - recordsets aren't evil.
(They can be brilliant, wonderful things).
Moving away from them as the constant fallback is correct, but they will always form some part of the solutions you implement in your applications.
For iterative processes that can be handled by a batch statement then sure - look elsewhere. But be sure that the process is being handled as a batch.

Cheers.

 

[AlexTeslin]

Hi Leigh,

Thanks for your reply.

About rst1.Open sqlVersion1, cn2
you should use
cn2.Execute sqlVersion1

I knew that was not quite right. But I have been using on my earlier codes when i was just writing records to Excel worksheets and it was returning records. I will change that anyway because in this instance is the wrong way.

In terms of connecting through OLEDB to Jet - I used it because I was given a sample code of how to do that task, but didn't work.

I have nothing against the recordsets myself. The reason I am trying to do through the query is performance. (I heard that doing this with recordsets will be very slow). I am not trying to copy whole tables, but there are about 20 queries to run at the time. So I thought using query will be much faster.

Do you think this is feasible task to implement (I mean with query). And if it is, is it only through OPENROWSETs or anything else is there? And can I kindly ask if you have any links to the material for research - I was googling for few days and NOTHING.

In the meantime, I will try with recordsets and see how long will it take.

Thanks again

 

[LPurvis]

Connecting through OLEDB to Jet is fine in itself - but you can't then expect Jet to use OLEDB credentials to establish a join to external data.

There are two operations at play in each instance here - and it's that duality that you're perhaps not aware of.

Firstly you're establishing an ADO connection to the platform database of your choice.
Let's take the second example.
strAccess = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=W:\Monitoring and Reporting\CopyToDatabase.mdb"
cn2.Open strAccess
You then have an established connection to your Jet database (you're using the Jet OLEDB provider - it's Jet your VBA code is working with via ADO).
You can use that connection to perform tasks in Jet - just as you would by executing SQL commands in queries when in Access. (Actually it's occassionally a bit different syntax-wise, in fact the Jet OLEDB provider exposes some Jet functionality that even Access can't use when interfacing with jet! But you can ignore that consideration for now.)
You're therefore in the position of executing statements on that connection which Jet supports.
Jet supports selecting from and updating external data sources with a properly formatted statement, specifying the driver to use if the source is not Jet (i.e. an MDB). In the case of SQL Server data this looks like
SELECT *
FROM [ODBC;Driver=SQL Server;Server=myServer\dbName;DATABASE=dbName;UID=myID;PW=myPassword].[person]
Where you can see you're using an ODBC connection string (as Jet can only use ODBC drivers internally).
If you stick to such a connection within your Jet statements then you'll be able to execute them as desired.

But bear in mind that the establishing of a connection to Jet and then what you have Jet execute are completely separate concepts and actions.
So you can't just re-use the strSQLServer connection string in your Jet based SQL.

Now in your subsequent SQL Server statements, I see you're actually using OpenDataSource, that's good - but you're not using it in the right place and you're also using the Jet syntax. An OLEDB connection string is valid in that function. But not in the SELECT statement you have following it. i.e.
SELECT person.person_ref FROM [" & strSQLServer & "].person
which is, again, the Jet syntax for selecting from an external source. i.e. it won't work. That's what the built in T-SQL functions are for.
If the data which you're selecting is in the SQL Server db then there's no need for the connection information - as you're already connected to it - it's local data.
SELECT person.person_ref FROM person
You'd use OpenDataSource (or of course OPENROWSET) to access (and update) the external data.
Giving you:
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=W:\Monitoring and Reporting\CopyToDatabase.mdb')...AccessTable (Test1)
SELECT person.person_ref FROM person

Anyway - that is hopefully more of a separation of technologies for you and enough to get you under way.

Cheers.