I am using a script of Ken's to insert data from my Excel to a table in my pervasive database. It works great , but i have come to realize that i need to check and make sure
that there are no existing records in my databse table that matches the data that i am about to insert. Long story short i do not need duplicate records in my database. I'll do my best to provide enough info.
Info:
my Name Range tblHeadings in Excel will have a column named Router_Number. This is the only column i need to check against the field Router_Number in my databse table. Of course there will be a Field in my database table named Router_Number. To sum it up the code works it's way down the Router_number column in my Excel Sheet and looks for duplicates or same numbers in the Router_Number field in my database table. I've tried doing a select query but just can't seem to get it to work. can someone help?
Thanks,
Here is the code:
that there are no existing records in my databse table that matches the data that i am about to insert. Long story short i do not need duplicate records in my database. I'll do my best to provide enough info.
Info:
my Name Range tblHeadings in Excel will have a column named Router_Number. This is the only column i need to check against the field Router_Number in my databse table. Of course there will be a Field in my database table named Router_Number. To sum it up the code works it's way down the Router_number column in my Excel Sheet and looks for duplicates or same numbers in the Router_Number field in my database table. I've tried doing a select query but just can't seem to get it to work. can someone help?
Thanks,
Here is the code:
Code:
'Author : Ken Puls ([URL="http://www.excelguru.ca"]www.excelguru.ca[/URL])
'Macro purpose: To add record to a Pervasive database using ADO and SQL
'NOTE: Reference to Microsoft ActiveX Data Objects Libary required
Dim cnt As New ADODB.Connection, _
rst As New ADODB.Recordset, _
dbPath As String, _
tblName As String, _
rngColHeads As Range, _
rngTblRcds As Range, _
colHead As String, _
rcdDetail As String, _
ch As Integer, _
cl As Integer, _
notNull As Boolean
'Set the string to the path of your database as defined on the worksheet
dbPath = ActiveSheet.Range("B1").Value
tblName = ActiveSheet.Range("B2").Value
Set rngColHeads = ActiveSheet.Range("tblHeadings")
Set rngTblRcds = ActiveSheet.Range("tblRecords")
'Concatenate a string with the names of the column headings
colHead = " ("
For ch = 1 To rngColHeads.Count
colHead = colHead & rngColHeads.Columns(ch).Value
Select Case ch
Case Is = rngColHeads.Count
colHead = colHead & ")"
Case Else
colHead = colHead & ","
End Select
Next ch
'Open connection to the database
' cnt.Open "Provider=PervasiveOLEDB;Data Source=demodata;Location=" & dbPath & ";"
cnt.Open "Provider=PervasiveOLEDB.;Data Source=demodata;User ID=Administrator;Password=Imnumber03;Location=lab-server;Cache Authentication=False;Encrypt Password=False;Mask Password=False;Persist Encrypted=False;Persist Security Info=False;Impersonation Level=Anonymous;Mode=ReadWrite;Protection Level=None;Port=1583;Pessimistic Read Lock=False;DirectOnly=False;LocalTCP=False;Initial Catalog=(Default)"
'Begin transaction processing
On Error GoTo EndUpdate
cnt.BeginTrans
'Insert records into database from worksheet table
For cl = 1 To rngTblRcds.Rows.Count
'Assume record is completely Null, and open record string for concatenation
notNull = False
rcdDetail = "('"
'Evaluate field in the record
For ch = 1 To rngColHeads.Count
Select Case rngTblRcds.Rows(cl).Columns(ch).Value
'if empty, append value of null to string
Case Is = Empty
Select Case ch
Case Is = rngColHeads.Count
rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
Case Else
rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
End Select
'if not empty, set notNull to true, and append value to string
Case Else
notNull = True
Select Case ch
Case Is = rngColHeads.Count
rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
Case Else
rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
End Select
End Select
Next ch
'If record consists of only Null values, do not insert it to table, otherwise
'insert the record
Select Case notNull
Case Is = True
'rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
' Dim sSQL As String
'sSQL = "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail
'rst.Open sSQL, cnt
Dim sSQL As String
sSQL = "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail
MsgBox "Query: " & sSQL
rst.Open sSQL, cnt
Case Is = False
'do not insert record
End Select
Next cl
EndUpdate:
'Check if error was encounted
If Err.Number <> 0 Then
'Error encountered. Rollback transaction and inform user
On Error Resume Next
cnt.RollbackTrans
MsgBox "There was an error. Update was not succesful!", vbCritical, "Error!"
Else
On Error Resume Next
cnt.CommitTrans
End If
'Close the ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
On Error GoTo 0
