validate import table primary key

[souris]

I want to import a table in to my access table, but want to make sure to
import right information.
Is it possible to validate the primary key field in the source table using
VBA?

I tried to validate the primary key, but I do not know how to assign the
primary key value to my variable.

I tried a query to "SELECT P_FIELD FROM MYTABLE" to see are there any
records in the source table, but I do not know how to access the record
count of the result set.

Am I on the right track?
Any suggestions?
Your information is great appreciated,

Souris,

 

[John Vinson]

I want to import a table in to my access table, but want to make sure to
import right information.
Is it possible to validate the primary key field in the source table using
VBA?

I tried to validate the primary key, but I do not know how to assign the
primary key value to my variable.

I tried a query to "SELECT P_FIELD FROM MYTABLE" to see are there any
records in the source table, but I do not know how to access the record
count of the result set.

You can do so in several ways:

SELECT Count(*) FROM MyTable;

and view the results of the query;

RecCount = DCount("*", "[MyTable])

in VBA code;

Open a Recordset, navigate to the last record with MoveLast, and look
at its RecordCount property.

I'm not certain what constitutes a "validated" primary key in this
context. How would you determine whether a primary key is valid or
not? What if the import table contains 32,995 records?


John W. Vinson[MVP]

 

[souris]

Is RecCount an integer variable in VBA?
DCount seems a function in Excel, does it work in Access?
My I do this like

MyResordSet = "SELECT * FROM MYTABLE"
If MyRecordSet.RecordCount = 0 then
' Do something
End If

Your information is great appreciated,

Souris,

I want to import a table in to my access table, but want to make sure to
import right information.
Is it possible to validate the primary key field in the source table using
VBA?

I tried to validate the primary key, but I do not know how to assign the
primary key value to my variable.

I tried a query to "SELECT P_FIELD FROM MYTABLE" to see are there any
records in the source table, but I do not know how to access the record
count of the result set.

You can do so in several ways:

SELECT Count(*) FROM MyTable;

and view the results of the query;

RecCount = DCount("*", "[MyTable])

in VBA code;

Open a Recordset, navigate to the last record with MoveLast, and look
at its RecordCount property.

I'm not certain what constitutes a "validated" primary key in this
context. How would you determine whether a primary key is valid or
not? What if the import table contains 32,995 records?


John W. Vinson[MVP]

 

[John Vinson]

Is RecCount an integer variable in VBA?

There's a Recordcount method of a Recordset object.

DCount seems a function in Excel, does it work in Access?

Yes:

If DCount("*", "MyTable") = 0 Then

My I do this like

MyResordSet = "SELECT * FROM MYTABLE"
If MyRecordSet.RecordCount = 0 then
' Do something
End If

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = db.CreateRecordset("SELECT * FROM MYTABLE WHERE ...", _
dbOpenDynaset
If rs.RecordCount = 0 Then
<do something>
Else
<do something else such as...>
iX = rs!FieldX
End If

John W. Vinson[MVP]

 

[souris]

Thanks millions,

Souris,

There's a Recordcount method of a Recordset object.


Yes:

If DCount("*", "MyTable") = 0 Then


Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = db.CreateRecordset("SELECT * FROM MYTABLE WHERE ...", _
dbOpenDynaset
If rs.RecordCount = 0 Then
<do something>
Else
<do something else such as...>
iX = rs!FieldX
End If

John W. Vinson[MVP]