asked on

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

Giovanni

Try GUID.

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

Open in new window

GPSPOW

ASKER

I am sorry. How does this fit into my VBA procedure?

Thanks

Glen

Jeffrey Coachman

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

GPSPOW

ASKER

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

Jeffrey Coachman

Not sure I understand, so lets wait and see what other experts reply with to fix your code

clarkscott

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

GPSPOW

ASKER

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

ASKER CERTIFIED SOLUTION

clarkscott

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GPSPOW

ASKER

That did it.

Thanks

Glen