Search results

Thank you Minty, indeed all the tables that were read only did not have a primary key set. I have not come across this before because I virtually always set a primary key. The offending tables were copied from another database some time ago for futureproofing and I must have forgotten to set...

I have an access database which connects to a series of tables on a SQL server. Each time the database is open All tables are linked using a function to create a DSN Less tables. But when I go to add or edit records, some tables allow this, others allow no edits or additions (yellow star at...

I solved the problem by adding another Or clause as below: If (KeyCode >= 48 And KeyCode <= 57) Or KeyCode = 8 Or KeyCode = 9 Or KeyCode = 46 Or (KeyCode >= 96 And KeyCode <= 105)Then

Thanks for that, quite ironic I had just created a simple form to print out the key code for each key pressed on the keyboard. It is consistent with the link you provide.

OK. I have now remapped all the main alpha-numeric key presses. What appears to be happening is when numbers are pressed on the numeric pad through "num lock", they give different and incorrect ASCII values, even though they themselves print fine on the screen! I am confused! Key typed = q...

I will write a little app that logs all codes and come back to you.

I have a function to prevent users typing non numerics into field requiring pure numbers. It uses the On KeyDown event to read the ASCII code for the key which has been pressed if the key entered has an ASCII code falling between 48 & 57 (corresponding to 0 through 9) then it is accepted, else...

It can be either depending upon the ordering of the "GROUP BY", if group by is written after FROM then it needs to be HAVING, WHERE throws an incorrect syntax error. Either of the below work fine in SSMS or Access: SELECT [I_PtNo], sum([SM_Qty]) as QR FROM [dbo].[tblItemRequest] GROUP BY...

I believe HAVING is necessary on an aggregate query WHERE throws an incorrect syntax error in SSMS. I have solved the ODBC Error (it isn't the best error message tbh) and it is my bad. I have just returned to work after 2 weeks of illness and I have had a mare of a 48hrs, it is like everything...

I use tons, I exclusively use unbound forms for both displaying data and editing/inputting data. The database contains no tables at all all data access to the SQL server is done with PTQ. So I will definitely give your suggestion a try.

Thanks Cheekybuddha, I had indeed been using single quotes, constantly switching from SSMS to VBA has scambled my brain, well worked out. It now executes, there is still a problem with the code as I am getting an ODBC call error on line 110. but at least I now know where to look.

Thanks for your input. The code compiles fine and I already tried adding breakpoint, the function does not even execute so even the first breakpoint is not reached. Cheekybuddha has guessed right.

I have a function that uses a query to a SQL datatase to determine the quantity of an item that has been reserved. The code will not run, if I use the immediate window and type ?getqtyreserved('I_000012') I get an error. "Compile Error: Expected: Expression". I have tried adding breakpoints in...

Solved it. I discovered that the global constant for the connection string wasn't initialising. so qdf.connection was null. It appeared to therefore be running the insert query on the linked local table which happened to have the same name as the SQL Server table. Because the insert query was...

OK so I did some experimenting. I created a new blank database, with no tables, no queries just a vba code module. I then created a series of "SELECT" querydefs in VBA some that created stored querydefs, some that did not: 'stored/saved querydef Set qdf = db.CreateQueryDef("qdfTemp")...

I did a bit of experimenting and it would appear you are right. I have linked tables in the database with the same name as those on the SQL server which have been masking what is going on. I was presuming the queries I had created in VBA were working as pass through queries directly on the SQL...

Your assumption is right. So to experiment I have just tried creating two saved pass through queries based on the string created by strSQL: INSERT INTO tblItemRequest ( [IR_ID], [I_PtNo], [StaffNumber], [IR_DateRequested], [IR_QtyRequested], [IR_Notes]) VALUES ('IR00000007', 'I_000012'...

Thank you, you solved it while I still have hair left! Access now executes the query on the SQLServer, adds the record and populates the date field, perfect! Though I still don't understand why all the other attempts ran fine on the SQL server when I run the SQL code directly on SSMS by...

What draws you to that conclusion?

Confused. If the query isn't using the SQL server engine, why is the record being added to the table on SQLServer. See first paragraph of the post "..The function runs without error and inserts a record, but the date field is blank...".