I have written SQL statements (stored in a text document) that load data into a SQL server database. These statements need to be repeated daily. Some of the statements use the NewId() function to populate a keyed field in the database, and this works fine. While I'm in the process of writing an application replicate these statements, I want to use access queries and macros instead of copying and pasting queries into SQL server, thus saving me time on a daily basis. All is working fine but I can't find any function that will replace the SQL NewId() function. Does one exist or is there a work around? I'm using SQL Server 2005 and access 2007.
A:
The only workaround I can think of would be to define the column in your access database of type "Replication ID" and make it an autonumber field. That will automatically generate a unique GUID for each row and you won't need to use newid() at all. In SQL server, you would just make the default value for the column "newid()".
Eric Petroelje
2009-03-03 17:29:34
GUIDs don't work well in Access: http://trigeminal.com/usenet/usenet011.asp?1033 . But they aren't needed -- all that's needed is an Autonumber.
David-W-Fenton
2009-03-04 04:55:35
Oops -- misread the original question (didn't realize NewID() specifically generated a GUID). GUIDs still don't work well in Access, though.
David-W-Fenton
2009-03-04 04:57:08
why don't GUIDS work well in Access??
Mitch Wheat
2009-03-04 05:04:34
FWIW the ACE/Jet keyword to generate a GUID is GenGUID() but AFAIK it can only be used with DEFAULT in SQL DDL e.g. CREATE TABLE Test (ID UNIQUEIDENTIFIER DEFAULT GenGUID() NOT NULL UNIQUE, data_col INTEGER);
onedaywhen
2009-03-04 09:42:41
To Mitch, read the article at the URL in my first comment.
David-W-Fenton
2009-04-02 20:44:03
+1
A:
Put this code in a module and use the function in your queries.
matt eisenberg
2009-03-03 17:30:48
A:
On top of matt's answer, you could simply use a pass-through query and just use your existing, working queries from MS Access.
Renaud Bompuis
2009-03-04 02:27:16
A:
Again, there seems to be confusion here.
If I'm understanding correctly:
You have an Access front end.
You have a SQL Server 2005 back end.
What you need is the ability to generate the GUID in the SQL Server table. So, answers taht suggest adding an AutoNumber field of type ReplicationID in Access aren't going to help, as the table isn't a Jet table, but a SQL Server table.
The SQL can certainly be executed as a passthrough query, which will hand off everything to the SQL Server for processing, but I wonder why there isn't a default value for this field in SQL Server? Can SQL Server 2005 tables not have NewId() as the default value? Or is there some other method for having a field populate with a new GUID? I seem to recall something about using GUIDs and marking them "not for replication" (I don't have access to a SQL Server right at the moment to look this up).
Seems to me it's better to let the database engine do this kind of thing, rather than executing a function in your SQL to do it, but perhaps someone can enlighten me on why I'm wrong on that.
David-W-Fenton
2009-03-04 05:01:40