April 10, 2012 at 5:56 am
I have some MS Access databases that are still used by another program, but I import this data into SQL for Reporting Services. Right now I have developed a way in Excel using VBA to import the data into spreadsheets and then export it to SQL. It works well, except the code and some information is exposed to the user even if I use Excel Security to lock the code down. I am trying to write a VB application to replicate the process. I have found some example code which uses the CommandBuilder to automagically create the insert statement and it works. However, the user must only select NEW data to insert. If any PK violation occurs, it won't do anything. I want to skip or ignore duplicate rows so I need to custom write an insert statement.
What the code does now is read from access and merge that data into into a dataset. Then the commandbuilder takes that dataset and imports it into SQL without any regard to PK violations.
Here is the code (three different subs)...
Private Sub RetrieveDataCMD_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RetrieveDataCMD.Click
Dim SourceConnectionString As String
Dim SourceTableName As String
Dim TargetConnectionString As String
Dim TargetTableName As String
Dim sourcesqlstring As String
Dim targetsqlstring As String
Dim targetwhere As String
Dim sourcewhere As String
Dim response As Integer
targetwhere = "" '"not exists (select * from meter_data_tablev2 where meter_data_dmd_time = meter_data_table.meter_data_dmd_time and station = meter_data_table.station)" '"meter_data_dmd_time > (select MAX(meter_data_dmd_time)from Meter_Data_Tablev2)"
targetsqlstring = "meter_data_dmd_time, meter_data1, Station, meter_data_meter_id"
sourcewhere = "meter_data_dmd_time between #03-01-2012# and #04-01-2012#" ' & TextBox2.ToString
sourcesqlstring = "meter_data_dmd_time, meter_data1, (select meter_name from meter_table) as Station, meter_data_meter_id"
SourceConnectionString = ("Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Program Files\Electro Industries\Communicator_Ext\Retrieved Logs\Battery Heights.EBM-DB")
SourceTableName = "Meter_Data_Table"
ExportTableData(SourceConnectionString, SourceTableName, oView, sourcesqlstring, sourcewhere)
TargetConnectionString = ("*****")
TargetTableName = "Meter_Data_Tablev2"
ImportTableData(TargetConnectionString, TargetTableName, oView, targetsqlstring, targetwhere)
End Sub
Public Sub ExportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
ByRef oView As DataView, _
ByVal SelectSQL As String, ByVal WhereSQL As String)
' Export Access table to data view
Dim strSQL As String
Dim oRS As DataSet
Dim oConnOleDb As OleDbConnection
Dim oCmdOleDb As OleDbCommand
Dim oDAOleDb As OleDbDataAdapter
Try
strSQL = ""
oCmdOleDb = Nothing
oDAOleDb = Nothing
oConnOleDb = New OleDbConnection(ConnectionStr)
oCmdOleDb = oConnOleDb.CreateCommand()
If SelectSQL.Length = 0 Then
strSQL = "SELECT * FROM " & TableName
Else
strSQL = "SELECT " & SelectSQL & " FROM " & TableName
End If
If WhereSQL.Length > 0 Then
strSQL = strSQL & " WHERE " & WhereSQL
End If
' Execute
oCmdOleDb.CommandText = strSQL
oDAOleDb = New OleDbDataAdapter(oCmdOleDb)
oRS = New DataSet
oDAOleDb.Fill(oRS, TableName)
oView = New DataView(oRS.Tables(0))
oConnOleDb.Close()
oConnOleDb = Nothing
oDAOleDb = Nothing
oRS = Nothing
oCmdOleDb = Nothing
Catch ex As Exception
TextBox1.Text = "Export Error: " & ex.ToString
End Try
End Sub
Public Sub ImportTableData(ByVal ConnectionStr As String, ByVal TableName As String, _
ByRef oImportView As DataView, _
ByVal SelectSQL As String, ByVal WhereSQL As String)
' Import table from data view
Dim strSQL As String
Dim oConn As SqlConnection
Dim oCmd As SqlCommand
Dim oDA As SqlDataAdapter
Dim oRS As DataSet
Dim oView As DataView
Try
strSQL = ""
oConn = New SqlConnection(ConnectionStr)
oConn.Open()
oCmd = Nothing
oDA = Nothing
oCmd = oConn.CreateCommand()
If SelectSQL.Length = 0 Then
strSQL = "SELECT * FROM " & TableName
Else
strSQL = "SELECT " & SelectSQL & " FROM " & TableName
End If
If WhereSQL.Length > 0 Then
strSQL = strSQL & " WHERE " & WhereSQL
End If
' Execute
oCmd.CommandText = strSQL
oDA = New SqlDataAdapter(oCmd)
oRS = New DataSet
oDA.Fill(oRS, TableName)
oView = New DataView(oRS.Tables(0))
' Set rowstates so that rows are inserted
oView.Table.Merge(oImportView.ToTable, True, MissingSchemaAction.Ignore)
' The trick is to use CommandBuilder to create INSERT statement "automatically"
Dim oCmdBuilder As SqlCommandBuilder
oCmdBuilder = New SqlCommandBuilder(oDA)
' Finally call update to commit changes to database table
oDA.Update(oView.Table)
oConn.Close()
oConn = Nothing
oDA = Nothing
oRS = Nothing
oCmd = Nothing
Catch ex As Exception
TextBox1.Text = "Import Error: " & ex.ToString
End Try
End Sub
April 10, 2012 at 9:22 am
How is excel helping you at all in this process? You'd have a lot better functionality on this if you were to keep this within Access.
As far as I can tell - the only thing custom about the insert is to ignore any existing PK's, which can be done if you were to add a linked table to the table you're inserting into. Use the linked table to then exclude any records that already exist in the target.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 10, 2012 at 11:50 am
Excel allowed me to export the data from the Access DB into SQL. The Access DB is actually not a real Access DB that was created with Access. It is created by another application every time the meter is read. I tried doing some linking tables within Access, but the application that writes the DB file is always writing into Access 97 format so my Access 2010 keeps wanting to convert it and then has issues if I don't and it's truly a long drawn out mess and story. Also what I have is a separate DB for each meter (21 total) so what I do with Excel is open each file and query the data, place it into a separate sheet for each file, and then export each sheet into SQL. It works very fast. The VB application also works very fast, but when I finally, just a few moments ago, stumbled onto the way to skip/ignore duplicates, the process time slows way down if there are a lot of records to be skipped. Each meter that is read has 96 data entries each day or about 2970/month and multiply that by 21 meters so I am importing about 80,000+ rows of data. It doesn't seem like much, but when I use the newly found (for me) tool of sqldataadapter.continueupdateonerror just skipping those 2970 rows takes several minutes so imagine if I goofed up and tried to import 80,000 duplicate rows. I would probably realize it right away, but my end user may not. What I haven't figured out with this new method is how to check first and see if the user really wants to perform the action. I'll get to that though I'm sure.
April 11, 2012 at 8:05 am
I have to agree that I don't get the Excel element. It seems like it is adding an extra to an already overly complicated process that just isn't needed. If I understand what you are doing you have a VB app that pulls data from 21 access databases and creates an excel spreadsheet to hold all that data. Then you import that data into sql and destroy the spreadsheet. Given that description there is no point in Excel at all. Just use VB to pull the data from your Access files and import it directly.
The whole process as you described it sounds like it could benefit from a total overhaul and simplification.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 9:43 am
No, I'm sorry, I must have not stated this correctly. I have an Excel sheet using VBA that already does what I need it to do. However, it exposes too much information and leaves a lot of room for tampering by the user (which use to be me only). I now want to create a VB application to do the same thing that the Excel sheet did and STOP using Excel. For Excel, I filled individual sheets that I create at "run time" (one for each meter) with the data and then exported that data to SQL. I did finally manage to accomplish a VB application yeseterday that now does it. I was missing an element to skip over duplicate rows that violated the PK in SQL. That method still isn't perfect because it takes a long time if you have too many violations. If the user screws up and tries to import 80,000 rows of duplicate data then he/she is going to be sitting for a while for that to stop.
Unfortunately I have to maintain these Access database files as they are the only way to get the data from the meters at this point using the vendor's software. The vendor does not write directly to SQL. If I knew more about how the vendor fetches the data from the meters using the Modbus TCP protocol and how to design an application using that protocol then I would completely get away from that vendor's application for meter reading. Modbus TCP is a protocol used by a lot of utility products like meters, protective relays, programmable logic controllers, and SCADA.
I have never had any formal classes in VB other than the traditional "Hello World" beginner class that did not teach much of anything. I was doing "Hello World" on my Commodore 64 with Basic in '82. Most of my VB and VBA is a hack job at best, but I get by. Some day I will find some time and some better classes/tutorials to get a better understanding. Some day....
April 11, 2012 at 9:49 am
Gotcha. So you are actually in the process of making this a better already. I certainly understand being stuck with horrible decisions by vendors, it seems that is the only decision they make. Are you looping through each row in Access and inserting to SQL? If so, you could just add a NOT EXISTS to your where clause. Then it would not insert the current row if it already exists in sql. If you are doing a set based insert (hopefully) and you are on sql 2008 you could use the MERGE statement. The next steps kind of depend on how your application is written and the version of sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 11:53 am
I pasted the code above and most of it is still the same. I added a progress bar and a few other things, but for the most part I am merging and using sqldataadapter.update to move the data. This apprently creates the INSERT command for me. I did a lot of looking around on the net for a row by row example, but it seems that most folks are using linked tables from Access to SQL or are directly converting their Access to SQL. There aren't too many people out there with such a special need as myself and what I did find I had to modify a little to get it to work for me. That's what I am best at doing, finding an example and modifying it to fit my needs. Because of having no formal training I don't seem to be able to think up these things on my own. Half the time I don't realize they even exist until I see it. I do web pages too that no one else seems to do, like getting real-time data from my SQL server and putting it all over the web page and not in pretty little tables. I use it to change images from one to another based on a true/false bit in the data so I know if a motor is running or not or a pump is running and things like that. I go out and find examples of how to pull data into VB code behind and then I figure out how to put it into lables using arrays for speed or temperature or changing image place holders based on those t/f bits. It's definitely not "standard" by any means and no one seems to understand my end goals. I have found some good help on this site though that eventually gets me where I need to start. If you know of a row by row method I would definitely take a look at it. Here's what I do in a nut shell:
Have the user either use the default directory where the Access files are stored or change a text box to tell me where they are. Then they click on a "get all files" button or "get selected" button. If they choose all then it looks in the directory and finds the file names, or if they select it only gets the file names and paths of the files selected. It puts those paths and names in a list box which I use to itterate through for each file and get the data then export it.
Right now I have a lot of commented out stuff in my code where I tried something that didn't work but thought maybe I could use it later or just left it for whatever reason. If you wanted the entire code I can paste it so you can see what's going on and maybe help. You would have to ignore some of my "beginner" hacks and crap that doesn't belong, but I'm not embarassed to post it. I can use all the help I can get. If you don't prefer to help I don't mind that either and I completely understand.
April 11, 2012 at 12:09 pm
Maybe you can use a left join in your ImportTableData method.
If SelectSQL.Length = 0 Then
strSQL = "SELECT * FROM " & TableName
Else
strSQL = "SELECT " & SelectSQL & " FROM " & TableName
End If
--before adding the where clause
strSQL = strSQL & "left join [DestinationTable] myAlias on
"
--then add your basic where clause
strSQL = strSQL & "Where myAlias.SomeJoinedColumn is null
--need to change to "And" instead now
If WhereSQL.Length > 0 Then
strSQL = strSQL & " AND " & WhereSQL
End If
This is just a shot in the dark but it sounds like it might be close enough to get you pointed in the right direction???
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 11, 2012 at 12:19 pm
I will give that a shot and let you know. It probably won't be until tomorrow though. By using "someone else's" code I don't fully understand it, but at first it appeared that it goes and gets the data from the Access file, then the SQL database, merges it to one table, then tries to import? Or so I thought. Then I thought that maybe it gets the Access data and merges to a blank table and then tries to import. The merge appears not to be concerned with PK at this point, no table structure it seems. So either way it tries to insert duplicates. Perhaps if it merges the actual SQL data with it then your join would be the ticket. I had tried using a where not exists but it seemed like it couldn't find anything to compare it to so that's why I thought maybe the merge was to an empty table. Maybe I just did it wrong. I will work with what you've given and see what I come up with.
Thanks for the assistance!!
Sean Lange (4/11/2012)
Maybe you can use a left join in your ImportTableData method.
If SelectSQL.Length = 0 Then
strSQL = "SELECT * FROM " & TableName
Else
strSQL = "SELECT " & SelectSQL & " FROM " & TableName
End If
--before adding the where clause
strSQL = strSQL & "left join [DestinationTable] myAlias on
"
--then add your basic where clause
strSQL = strSQL & "Where myAlias.SomeJoinedColumn is null
--need to change to "And" instead now
If WhereSQL.Length > 0 Then
strSQL = strSQL & " AND " & WhereSQL
End If
This is just a shot in the dark but it sounds like it might be close enough to get you pointed in the right direction???
April 11, 2012 at 12:21 pm
Happy to help. Post back when you have a chance to try it and let us know. Good luck!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2012 at 5:36 am
Well I tried a few different things a few different ways. I'm probably not doing this correctly but my confusion is in what to "call" the table from the data that was taken from the Access file. I know what the table name in Access is and what it is in SQL, but it seems like once the data is taken out of Access and put into a dataset/datatable in the application, that it must have some other name. The error response I am getting is quite long, but mostly seems related to "ambigous coulmn name".
So the actual table name in Access is really the same as in SQL except that I am working with a production database in SQL. Access Table: meter_data_table SQL Table:meter_data_tablev2.
I recreated the meter_data_table in SQL and added v2. This way I can screw around with it without worrying about data contamination. Here is the code that I changed:
If SelectSQL.Length = 0 Then
strSQL = "SELECT * FROM " & TableName
Else
strSQL = "SELECT " & SelectSQL & " FROM " & TableName
End If
strSQL = strSQL & " left join meter_data_tablev2 mdv on mdv.station=station "
strSQL = strSQL & " where mdv.meter_data_dmd_time is null "
If WhereSQL.Length > 0 Then
strSQL = strSQL '& WhereSQL
End If
I commented out the WhereSQL since I wasn't using it at this time. It was just a part of the original design I grabbed from the web. I tried join on meter_data_table and the above and used an alias mdv and did not use an alias and I tried putting tablename.column on both columns and then not. Nothing seemed to work. Either something is wrong with my join or I am calling the wrong table(s). Also, using a join where tables are identical in format confuses me. Don't you normally join two tables that have some similar columns for the join, but also contain "extra" data that you want in your joined table? I'm not sure this is the right path, but since I can't get it to work it's hard to tell.
Some sample data looks like this in either table since they are identical:
[meter_data_dmd_time]-------[meter_data1]---[station]-------[meter_data_meter_id]
2012-03-01 00:00:00.000-----15.30------------bheights-------0
2012-03-01 00:15:00.000-----15.43------------bheights-------0
and so on using 15 minute intervals hour after hour, day after day.
The primary keys are meter_data_dmd_time AND station so I can have two duplicate 'times' as long as the stations are different. The meter_data1 represents a megawatt load on a station and the meter_data_meter_id isn't used at this point and is "left over" from the vendor's stuff. Until I can figure out what it actually represents I didn't want to throw it out. Looking at the sourcesqlstring you can see that I get 3 columns from one table in the Access file and one column [station] from another table in that same file and combine them into my dataset. I don't know if that makes a difference or not.
Sean Lange (4/11/2012)
Happy to help. Post back when you have a chance to try it and let us know. Good luck!!!
April 12, 2012 at 7:42 am
You just need to alias your original tables.
If SelectSQL.Length = 0 Then
strSQL = "SELECT * FROM " & TableName & " as SourceTable "
Else
strSQL = "SELECT " & SelectSQL & " FROM " & TableName & " as SourceTable "
End If
Then you can reference the alias in your join.
strSQL = strSQL & " left join meter_data_tablev2 mdv on mdv.station=SourceTable.station "
That make sense?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2012 at 8:37 am
Still getting: Ambiguous column name 'meter_data_dmd_time'.
Ambiguous column name 'meter_data1'.
Ambiguous column name 'Station'.
Ambiguous column name 'meter_data_meter_id'.
I'm not sure what to think. It seems to me like the joining is being done between the SQL table that exists on the server and the newly created dataset's table which doesn't really have a name. The data from the Access file is being dumped into that dataset so a new table is created. I wish I understood this a little better, I apologize.
Again, I appreciate your help.
April 12, 2012 at 8:45 am
The ambiguous column name means you need to specify which table it is coming from. Add the table alias to each column.
The join is from Access to SQL so you can not insert ones that already exist. This is kind of like a self join since the table structures are the same. The whole point of doing this with a left join is you only want the rows that don't match. If there is no match that means that the row in the source does not already exist in sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 12, 2012 at 9:39 am
Okay, I've done so many different things I'm starting to get myself confused so let me make sure I have it down.
here is my select command with the join:
select [sqlaliasname].meter_data_dmd_time, [sqlaliasname].meter_data1, [sqlaliasname].station, [sqlaliasname].meter_data_meter_id
FROM [SQL Real Table Name] as [sqlaliasname]
LEFT JOIN [Access Real Table Name] [accessaliasname]
ON ([accessaliasname].station=[sqlaliasname].station)
I just used the [name] to keep things straight so I hope it helps.
First, do I have the naming correct for the Join? Second, should I join on both PK columns or is one sufficient?
By doing it this way, I am rid of the ambiguos column name errors, however, the application hangs and I have let it go for as long as 2 or 3 minutes before stopping it. I queried my SQL table and no records were entered. The SQL table is currently empty so importing new records (about 3000) should take a second or two at best. If all the rows were duplicates then it was taking about 20 seconds to "skip over" the duplicates doing it the other way. I think you're getting there and likely the problem is my own confusion and I appreciate your patience. :hehe:
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply