SELECT INTO with Identity - getting error (1 Viewer)

phinix · Dec 4, 2019

[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?

theDBguy · Dec 4, 2019

You don't say newID=IDENTITY(1,1). You just simply say newID.

phinix · Dec 4, 2019

theDBguy said:
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?

theDBguy · Dec 4, 2019

phinix said:
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?

phinix · Dec 4, 2019

theDBguy said:
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.

theDBguy · Dec 4, 2019

phinix said:
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.

phinix · Dec 4, 2019

theDBguy said:
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?

phinix · Dec 4, 2019

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

theDBguy · Dec 4, 2019

phinix said:
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.

phinix · Dec 4, 2019

theDBguy said:
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]"

theDBguy · Dec 4, 2019

phinix said:
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.

SELECT INTO with Identity - getting error (1 Viewer)

phinix

Registered User.

theDBguy

I’m here to help

phinix

Registered User.

theDBguy

I’m here to help

phinix

Registered User.

theDBguy

I’m here to help

phinix

Registered User.

phinix

Registered User.

theDBguy

I’m here to help

phinix

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread