Export to SQL Server, Then Run ADODB Connection

[kjv1611]

I want to export a small table to a SQL Server on a recurring basis, and then run an ADODB connection (or something else if something else works better) against the SQL Server to be able to compare the exported table against a couple existing SQL tables.

Am I on the right path with this, and what is the best way to export from Access to a SQL server, via VBA. I specifically want to do the whole thing in code.

I'll probably find while digging that I already have learned how in the past, but I just can't remember.

Thanks for any thoughts/suggestions.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SoCalAccessPro]

Hmmm....

SQL Server, IIRC, has Distributed Transactions, which are for importing and exporting data.

Does the table already exsist on the server ? If not, look into the Create Table SQL Statement. You'll need rights, of course, and so will your users.

Can you explain a little more what you're trying to do ? Your current approach seems a but cumbersome.

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[kjv1611]

Okay, I'll tell you the full scope, and where I'm at currently, to get the best understanding.

Monthly, I am currently getting an Excel workbook with 91 or 92 worksheets supposedly all formatted the same (which they are not), so I run some code in Access to import all these, and then combine them, get rid of "blank" records, and get rid of duplicates of already processed records.

Everything to this step works great. Next, I need to bump the new data each month up against a couple of tables on a SQL server. At first, I thought the easiest/best way would be to link one table from SQL server, and just do it in Access. However, I then remembered that I needed to compare more than just one existing table on the SQL server, and none of those tables are exactly small.

And actually, b/c of the structure of the data with my permissions (or anyone else in my group who might use this), I probably will have to run this in 2 queries on the SQL Server, b/c joining certain views can tend to really bog the server down.

Hopefully that helps a little.

Let me know if you need further than that. It may take a while if so.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SoCalAccessPro]

Why not just have one table on the SQL server ?

I'm assuming that after you scrub your data, that the data is in a cosistant format (Feild names, types, etc).

If so, just add a column to your exsisting table and include the import date. Then, it's simply a matter of opening 2 recordsets and looping through one while you add to the other.





Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[PWise]

Dim db as Database
Set db=currentdb
db.execute "INSERT INTO [ODBC;DRIVER=SQL Server;SERVER=SERVERNAME;DATABASE=DBNAME].TABLEName ( FIELD1,FIELD2 )
SELECT FIELD1,FIELD2
FROM LOCALTABLENAME"

this code will export your code from access into SQLSERVER

 

[kjv1611]

I suppose the size of the table would be small enough that it wouldn't matter if I did create just one table on SQL server, and then append the data each month - such as using the code pwise suggests, along with an import date to differentiate, which I'll already have in the Access database, anyway.

Thanks to you both. I'll give it a try, and post back. If not this afternoon, then Lord willing, tomorrow morning.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

pwise,

I'm getting this error when trying to run that code:

Run-time error '3134':
Syntax error in INSERT INTO statement.

Of course, I substituted the correct details for the server name, database, name, table name, but still getting errors.

Do I need to run this through some sort of ADODB connection string instead of through the db.Execute method? It seems I did something like this in the past....

I'll be looking to see what I can find..

--

"If to err is human, then I must be some kind of human!" -Me

 

[cmmrfrds]

You can do what you orginally suggested through an ADO connection. SQL Server has a function called OPENROWSET that will take information you send it about your access database and the tables to be used in the query. It is like sending the SQL to SQL Server and then letting SQL Server make a connection to your access database to do the joins in the query. This way the query is processed on SQL Server which should have plenty of power and returns the completed resultset back. Here is an example against the Northwind database.


Public Function rowset()

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function

 

[kjv1611]

cmmrfrds,

Thanks for that suggestion. It sounds like just what I want to do. I'll give it a try hopefully w/in the next couple of hours, and post back.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

cmmrfrds,

I have to say I'm sorry. I did not get around to trying this out before, apparently, and didn't post back. I may use it with something today or in the next couple of days, however, so I'll post my results now. It might even be for the same situation as before, but I don't remember.

Please accept my appologies. My only guess is that 1. I got too tied up in other matters, and didn't have the time, probably in a combination with 2. I couldn't see this under "My Replies". I've noticed over the past several months that some things show up and others do not - weird, I think.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

I realize this is old, but I just now got around to trying to implement cmmrfrds suggestions. And so far, I'm getting one error that seems to be familiar, but I don't have the foggiest as to what is going on.

The code which is giving the error is this line:

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

And the error given is:

[Microsoft][ODBC SQL Server Driver][SQL Server]The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

The connection string portion that mentions the Microsoft jet is:

    sqlString = sqlString & _
                    "FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _                                "'\[I]FILEPATH[/I]';'admin';,qryToDwh) f "

I tried registring the jet40 dll file via the regsvr32 command using Start->Run, and then selecting the file by browsing to it from the References dialog in VBA, but it just says it cannot be registered..

I think that it's something to do with the connection syntax, but I can't think for the life of me what it is.

Any clues/thoughts?

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Have a look here:

http://www.connectionstrings.com/

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Am I missing something? I didn't see anything that is helping there. I've looked at

http://www.connectionstrings.com,

in the VBA helpfile, in the SQL Server help file, and on the web in general, nothing.

The syntax seems to be correct from what I can find.

I've also tried using the UDL wizard to verify my syntax, but it doesn't seem you use the exact same syntax when using the OpenRowSet method.

Is there some small something I'm missing, or do I just need to drop the ADODB Connection method for this, and try some other method?

Here's the full code of what I have, aside from the query portion, since that isn't the problem:

Private Sub cmdBumpDw_Click()
[HIGHLIGHT]Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
    
connString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=dw"
cn.ConnectionString = connString
cn.Open connString[/HIGHLIGHT]
    
sqlString = sqlString & _
  "SELECT [TableAlias].[i]fields[/i] "
sqlString = sqlString & _
  [HIGHLIGHT]"FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
  "'[i]filepath[/i];ReadWrite|Share Deny None;Persist Security Info=False',qryToDwh) AS f " & _[/HIGHLIGHT]
"Left Join " & _
"OtherTables"
        
[HIGHLIGHT]rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly[/HIGHLIGHT]
    
'...Apply the queried data to the table I am wanting to update in Access...
    
MsgBox "Applicable data retrieved from the Data Warehouse (dw)", vbInformation, "dw Operation Complete!"
    
ExitSub:
On Error Resume Next

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    
    Exit Sub

ErrHandle:
    ErrTalk (txtImportID)
    Resume ExitSub
                                
End Sub

Can anyone give me any sort of clue? If no one has any ideas, I'll just have to once again put this piece on the back burner, and just run SQL Server portions all manually. Surely there's something small/simple in this that I'm missing.

Any thoughts/ideas at all out there?

Thanks in advance..

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

As a follow-up attempt, I thought I'd attempt from the SQL Server side.

So, just to be sure, I thought I'd Google for some code.

I found this web page:

http://www.sqlhub.com/2009/03/read-xls-or-mdb-file-with.html

From there, I put together this code:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
		'Data Source =[I]FILEPATH[/I]')...[[i]QueryName[/i]]

And I'm still getting the same error as in Access:

Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.

So, I can only fathom one guess so far. Is this error coming up b/c I'm trying to export a Query instead of Table in Access? Does it somehow work differently via this method? I know I can just right-click on a query object, and export it to SQL Server, so I thought that it would work the same here as well.

--

"If to err is human, then I must be some kind of human!" -Me