SELECT INTO with Identity - getting error

phinix

Registered User.
Local time
Today, 14:33
Joined
Jun 17, 2010
Messages
130
[SOLVED] SELECT INTO with Identity - getting error

I'm getting an error (Undefined function 'IDENTITY' in expression) when trying to run this line:

Code:
strSQL = "SELECT newID = IDENTITY(1, 1), Data.Company, Data.Title, Data.Fullname INTO [Data Export] FROM Data ORDER BY Data.ID"

Also tried COUNTER, same error.
What am I missing?
 
Last edited:
You don't say newID=IDENTITY(1,1). You just simply say newID.
 
You don't say newID=IDENTITY(1,1). You just simply say newID.

But this Identity field is new, I'm adding it first, then take fields from other table. When I got rid of =, it didn't work.
Tried newID Identity(1,1),... or Identity(1,1) as newID.
Nothing worked.

Do I have to somehow declare this function first?
 
But this Identity field is new, I'm adding it first, then take fields from other table. When I got rid of =, it didn't work.
Tried newID Identity(1,1),... or Identity(1,1) as newID.
Nothing worked.

Hi. I just saw what you mean from your other thread. Are you wanting to add a sequential number to the exported data beginning with one?
 
Hi. I just saw what you mean from your other thread. Are you wanting to add a sequential number to the exported data beginning with one?

Yes, first I want to create this new table from data from other table, but as well add first ID which would be autonumber.
I wanted to do it at one step.
Next would be export, but first I need this table created.

Of course I could run second sql line and alter table adding it, but I want it to be in SELECT INTO sequence.
 
Yes, first I want to create this new table from data from other table, but as well add first ID which would be autonumber.
I wanted to do it at one step.
Next would be export, but first I need this table created.

Of course I could run second sql line and alter table adding it, but I want it to be in SELECT INTO sequence.

Hi. Unfortunately, there is no "one step" if you want an autonumber field and have it start at one in a make-table query. Instead, you could try using an Append query or add a sequencing function to your Select query.
 
Hi. Unfortunately, there is no "one step" if you want an autonumber field and have it start at one in a make-table query. Instead, you could try using an Append query or add a sequencing function to your Select query.

I wanted to run this but for some reason it doesn't work.

https://docs.microsoft.com/en-us/sq...y-function-transact-sql?view=sql-server-ver15

But ok, I will run in steps, first create table with Autoincrement and other fields, then add data.


Another question: how can I check if table exists and if it does, delete it?
 
Last edited:
I got this, but gives me error that Object required...


Code:
Dim strTableName As String
Dim TableExists As Boolean
Set TableExists = IsObject(CurrentDb.TableDefs(strTableName))

If TableExists("Data Export") = True Then
DoCmd.DeleteObject acTable, [Data Export]
End If
 
I got this, but gives me error that Object required...


Code:
Dim strTableName As String
Dim TableExists As Boolean
Set TableExists = IsObject(CurrentDb.TableDefs(strTableName))

If TableExists("Data Export") = True Then
DoCmd.DeleteObject acTable, [Data Export]
End If

Hi. Since you declared TableExists as Boolean, you don't Set it.
 
Hi. Since you declared TableExists as Boolean, you don't Set it.

taking off Set, gives me error Expected Array

EDIT:

OK, I got it working.
I did a separate function and called it later.

Code:
Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function

Then...

Code:
If TableExists("Data Export") = True Then
CurrentDb.Execute "DROP TABLE [Data Export]"
 
Last edited:
taking off Set, gives me error Expected Array

EDIT:

OK, I got it working.
I did a separate function and called it later.

Code:
Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function
Then...

Code:
If TableExists("Data Export") = True Then
CurrentDb.Execute "DROP TABLE [Data Export]"
Hi. Sorry for the delay, I was on the road. Glad to hear you got it sorted though. Good luck with your project.
 

Users who are viewing this thread

Back
Top Bottom