X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> SQL Backend--access Frontend, Any Versions    
post Feb 1 2019, 03:28 PM

Posts: 777
Joined: 31-May 04
From: Wisconsin, USA

Using 2017 SQL Version with Access 2016 (Office 365). We are moving our Access back-end to SQL, but I don't know SQL Server much better than Access! So bare with me, please. Eventually we plan on moving away from Access as a front end as well--baby steps!

So working in a test database with data from our current database, I converted this access query into SQL server:

--Get Max of Numeric Number
SELECT CategoryTypeID, CategoryID, SubcategoryID, Max(NumericNo) as MaxNo,
    Case When Max([NumericNo]) IS NULL THEN '1'
    Else Convert(decimal(6,0),Right(Max(NumericNo),6))+1
    End AS NextNumber
GROUP BY CategoryTypeID, CategoryID, SubcategoryID;

When we create a new part in the database--depending on the Category Type, Category and Subcategory the users choose, the next number in that "group" is assigned to the new part created. The NumericNo is made up of CategoryType, Category, SubCategory and the next numeric number in this format: XXX-000000 which is the "FORMAL" part number.

On the Access part form in our current database, this number is assigned after user clicked the save button and it would populate from the Access query.

Now, how do I get this Next Number from the SQL server for my Access form in my test database? Keep in mind that it needs to align with the Category ID's in the group by clause above.

I appreciate any help as well as any suggestions.

I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
post Feb 1 2019, 03:38 PM

Posts: 2,828
Joined: 27-February 09

Sounds like maybe you want to use a SEQUENCE?
Go to the top of the page
post Feb 1 2019, 06:08 PM

Posts: 2,828
Joined: 27-February 09

Just say no to "intelligent keys". They're just the biggest pain in the world. If you have an autonumber/autoincrement primary key, that guarantees uniqueness, and nothing more. If you want some pretty intelligent key, you're going to have to build it yourself. But WHY? Yes, autonumbered keys can have gaps, but so what? Users should never see them anyway.

The big thing with SQL Server is that all tables should have Primary Keys. (Otherwise they're heaps, and performance is gonna make you cry).
Go to the top of the page
Jeff B.
post Feb 1 2019, 07:33 PM

UtterAccess VIP
Posts: 10,203
Joined: 30-April 10
From: Pacific NorthWet

Bear in mind that you can store individual data elements that you then concatenate for display into what looks to users like a "sequential number".


Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
post Feb 1 2019, 09:34 PM

Posts: 777
Joined: 31-May 04
From: Wisconsin, USA

The Part table is currently set up like this:

PartTypeID--FK, not null
CategoryTypeID--FK, not null
CategoryID--FK, not null
SubcategoryID--FK, not null
PartNo--nvarchar(25)-- (This field will be eliminated in the near future), null
PartDescription--nvarchar(130), not null
DesignNo-nvarchar(10)--This field is for our Design Department, null
DesignNumeric--nvarch(10)-- Formatted into XXX-123456 (different set of numbers chosen by the Design Department and only used to match up to their database), null
NumericNo--nvarchar(10)--Formatted into XXX-123456 (where XXX is CatType/Category/Sub - next highest number in group), null
ItemNo (computed field consisting of NumericNo or DesignNo as one or the other must have a value in it)
UOMID--FK, not null
JobID--FK, null
GLID--FK, null
Cost--Decimal(10,6), not null
Retail--Decimal(10,2), not null
ImageName--nvarchar(25), null
CustomerID--FK, null
VendorID--FK (Preferred Vendor), null
HTSID--FK, null
FIXID--FK, null
StatusID--FK, not null
DC--Date, not null
CB--nvarchar(20), not null
DM--Date/Time, null
MB--nvarchar(20), null
A2016ID--FK (Ties PartID to old Access back-end), null

The query that I use in our current database gets the next available number for the CatType-Category-Sub group and it doesn't pose a problem. While I would like it sequential, it doesn't have to be 100%. A few little gaps here and there won't cause a problem. I don't allow Parts to be deleted unless it turns out to be a duplicate and then only I am able to delete the row. The StatusID allows for Active, Delete, and Inactive. When a user puts a part into a Delete status--the part is hidden from the users--but it still resides in the database. So when getting a list of parts in one CatType-Category-Sub it won't always be sequential as a deleted part won't be visible in the list. And, in some cases, I also hide the inactive parts from view (like trying to enter it into a new purchase order).

I would try to use a sequence, but in reading about it, I don't think I could get it to work the way I need it to. We have 9 category types and 900 Categories and each of those 900 categories can (at this time) have 999,999 numbers starting at 1. So category type 1 and category 0 and subcategory 0 would be 100-000001 to 100-999999. The next group would be 101-000001 to 101-999999, etc. The left 3 digits of the numeric number tells us all what we need to know about the part. This is a Fastener (Category Type), Thread Cutting Screw (Category) and Wood Screw (Subcategory). I seriously doubt we would ever need to add 999,999 wood screws to the database--but, hey, never say never. So a NumbericNo on a part might be 101-000058 (which, in a perfect world, would mean that we have 58 different size wood screws in our database, but in reality, it means we might have as many as 58 wood screws that we have to look at before deciding whether we need to make a new part, which would be 101-000059). In this particular category, I know there were several duplicates, so we really on have approximately 20 different wood screws.

The query that I converted gets the next number I need for the Category group. I just need to understand how to get it for my part form in access front end. Do I turn that converted query into a view, a function or a stored procedure on the SQL server? With a view, I can figure out how to get it on my form, but fuzzy on the function/store procedure.

Thank you for the help!

I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
post Feb 1 2019, 10:32 PM

Posts: 2,828
Joined: 27-February 09

I think I got it... Here's a quick example that might help:

SELECT FORMAT(123456789,'###-##-####')
SELECT FORMAT(1234,'000-00-0000');

USE AdventureWorksDW2016;

DECLARE @NextParentAccount TINYINT;
SELECT @NextParentAccount = 1 + MAX(a.AccountKey) FROM DimAccount a;
PRINT @NextParentAccount;
PRINT FORMAT(@NextParentAccount,'00000');

If you wanted to, you could write a function to return the various numbers and increment the ones you need to and then format them. Since format basically converts a number to a string, you can then do something like this:

PRINT 'X' + FORMAT(@NextParentAccount,'00000');

You could do something like
DECLARE @FinalResult VARCHAR(20);
SET @FinalResult = 'X' + FORMAT(@NextParentAccount, '000000') + '-' + 'B';
PRINT @FinalResult;

T-SQL works a lot like VBA where you can declare variables and then assign values to them and then use them later. So you could declare a few variables, and assign values to them using SELECT (if you're getting the value from a table and then manipulating it). Then you can use FORMAT or CAST to change the datatype to a string/varchar and then use + to concatenate the pieces.
Go to the top of the page
post Feb 5 2019, 07:54 PM

Posts: 431
Joined: 4-December 02
From: NSW Australia

not to hijack this post -but I will be doing exactly the same thing
I know you can link to SQL just like you can in access and not have to change one query
however as I understand it - access still performs the query,so the server traffic doesn't really change,and all data to perform the query flows across the network from SQL to access front end

My question is this
if you use ADOB connection does that return only the result of the query sent to SQL or is it the same as using an access query
Dim cn As Object
Dim rs As Object
Dim n As Long
Dim strQuery As String

Set cn = CreateObject("ADODB.Connection")

With cn
.Provider = "sqloledb"
.ConnectionString = "SERVER=DESKTOP-2KDQQUC\STDSERVER;Initial Catalog=material;Integrated Security=SSPI"
End With

strQuery = "SELECT TOP 1 * FROM MaterialParameter ORDER BY [ID] DESC"

Set rs = CreateObject("ADODB.Recordset")

With rs
.Open strQuery, cn, 1, 3
GetNextSql_ID = ![ID] + 1
End With

Set rs = Nothing
Set cn = Nothing
Go to the top of the page
post Feb 5 2019, 08:02 PM

Access Wiki and Forums Moderator
Posts: 74,497
Joined: 19-June 07
From: SunnySandyEggo

Hi. I believe it’s the same, but would like to hear from others.

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
post Feb 6 2019, 05:49 PM

Posts: 431
Joined: 4-December 02
From: NSW Australia

I guess I can test it by running the query's a few thousand times (or more) and time them
Go to the top of the page
post Feb 6 2019, 06:02 PM

Posts: 2,828
Joined: 27-February 09

If you are going to test that and want it to be accurate, you'll need to do something like


between executions. Otherwise, the first time the query is run, the data it uses will be read from the tables and loaded into memory (processor cache). Then all subsequent queries will use the cached data unless something else forces the data out (like memory pressure). Access and SQL Server are similar in a lot of ways, but also very different.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    21st February 2019 - 11:39 PM