Add unique ID # to an Access Data Table

The following query works as it is supposed to.  It appends records to a  MS-Access accdb file from an Excel sheet.

What I want to do is designate each new record with a unique record id.  I tried creating the access table with an AutoNumber primary key.  However, when I did the append VBA from Excel, I got a number of records with just the Primary Key autonumber id.  None of the data from the Excel sheet.

Here is the code:

Sub addtoaccess_adob()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, i As Long, LastRow As Long
' check this link too http://support.microsoft.com/kb/283874
Set cn = New ADODB.Connection
'in refrence Microsoft ActiveX Data Objects 2.1 Library or later version
'Microsoft ADO Ext. 2.1 for DDL and Security or later version
' C:\Documents and Settings\ashish\Desktop\excel to access\akoul123.accdb path of database
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & " Data Source=c:\users\Glen\Documents\NHSN.accdb;"
Set rs = New ADODB.Recordset
'ashish is table name
rs.Open "tbl_NHSN", cn, adOpenKeyset, adLockOptimistic, adCmdTable

LastRow = Sheets("tbl_NHSN").Range("a2").End(xlDown).Row
For i = 2 To LastRow
With rs
 .AddNew
 .Fields("Surgeon") = Range("A" & i).Value
 .Fields("MedRec") = Range("B" & i).Value
 .Fields("ProcDate") = Range("C" & i).Value
 .Fields("SurgeonID") = Range("D" & i).Value
 .Fields("PTName") = Range("E" & i).Value
 
 .Update
End With
Next i

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
MsgBox "Records Updated"
End Sub


Can I keep the Access table having an AutoNumber field and tweak a table property?
Otherwise, how can I input a unique record number into the table each time the table has a record added?

Thanks

Glen
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Giovanni HewardCommented:
Try GUID.

String myKey = Guid.NewGuid().toString();

Open in new window

0
GPSPOWAuthor Commented:
I am sorry.  How does this fit into my VBA procedure?

Thanks

Glen
0
Jeffrey CoachmanMIS LiasonCommented:
I am sure that an expert can get your code sorted...

In the mean time, have you considered linking the Excel data into Access?
(You can still input and edit the data in Excel,...)

This would save you the trouble or appending records forever...
(and worrying if you really have the most recent data)

JeffCoachman
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

GPSPOWAuthor Commented:
Thanks

The excel is not saved and is modified when used.  So the we create a sample in Excel, and export the sample information into Access to track responses later.

Glen
0
Jeffrey CoachmanMIS LiasonCommented:
Not sure I understand, so lets wait and see what other experts reply with to fix your code
0
clarkscottCommented:
Put a break point right before you addnew and step through your code.  Check to make sure your actually assigning values to your fields.  

It's not the autonumber that's causing your "empty records".


Scott C
0
GPSPOWAuthor Commented:
Sub addtoaccess_adob()

Dim cn As ADODB.Connection, rs As ADODB.Recordset, i As Long, LastRow As Long
' check this link too http://support.microsoft.com/kb/283874
Set cn = New ADODB.Connection
'in refrence Microsoft ActiveX Data Objects 2.1 Library or later version
'Microsoft ADO Ext. 2.1 for DDL and Security or later version
' C:\Documents and Settings\ashish\Desktop\excel to access\akoul123.accdb path of database
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & " Data Source=c:\users\Glen\Documents\NHSN.accdb;"
Set rs = New ADODB.Recordset
'ashish is table name
rs.Open "tbl_NHSN", cn, adOpenKeyset, adLockOptimistic, adCmdTable

LastRow = Sheets("tbl_NHSN").Range("a2").End(xlDown).Row
For i = 2 To LastRow

With rs
 .AddNew
 .Fields("Surgeon") = Range("A" & i).Value
 .Fields("MedRec") = Range("B" & i).Value
 .Fields("ProcDate") = Range("C" & i).Value
 .Fields("SurgeonID") = Range("D" & i).Value
 .Fields("PTName") = Range("E" & i).Value
 
 .Update
End With
Next i

rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
MsgBox "Records Updated"
End Sub

Okay.

I ran the code again and the value is not being assigned to the fields in the Access table.

A record is being created for every Excel line but no values are transferring over.

What should I look for in the Access table properties or change in the VBA to get the Access to update with the values?

Thanks

Glen
0
clarkscottCommented:
Try this:

.Fields("Surgeon") = Sheets("tbl_NHSN").Range("A" & i).Value

Scott C
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
That did it.

Thanks

Glen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.