Ahh, that may be the problem then... I have an index set on that serial number field for faster retrieval.
Thanks for the info.
dbrother
Last Activity: Aug 26 '09, 09:03 PM
Joined: Nov 1 '07
Location:
-
I have the ID column which is the unique identifier for each record. What I mean to say is how do I get SQL Server to actually store the data by the ID field?
No matter how I sort the data in the linked table in Access, every time I run the query and halt the code at the first position (last record), sample record with ID # 2 is always the last record. This is obviously due to the fact that SQL server is "storing" or "sorting"...Leave a comment:
-
If you are using passthrough queries in your Macro, you should be able to:
- open the PT query in Design View
- then click properties
- then click the build button at the end of the line that says "ODBC Connect Str" ( it will look like [...] )
Once you click that button, it will ask for your ODBC connection, choose it and it should open a box that will allow you to enter your password. Enter your password...Leave a comment:
-
How Access reads data stored in SQL Server
I guess my real question is how is data stored in SQL Server... I have a linked table in Access that I'm using ADO Recordsets in VBA to move to the last record. The code works fine, but I'm not getting the "correct" results.
Sample table:
...Code:ID Serial# Date --- ------- ------- 1 A123 08/21/09 2 S901 08/24/09 3 F789 08/23/09
-
Thanks! That solved it.
I thought that I'd used the sql string like that before, where it was altered later and then the variable replaced itself, but it doesn't seem so....
Thanks again.Leave a comment:
-
Nope, it's called in line 51 when says to execute the strSQL1 statement. You have to set the SQL string to something before you call it else you receive an error stating so. It's like declaring a variable before you use it.Leave a comment:
-
Lines 29-50 check 2 textboxes (txtPartNum and txtQty) for null and not null values and gives the appropriate error message box if necessary. The last else (lines 49 and 50, which means all inputs are valid) sets the value for strPartNum and then adds the % wildcard character.
Thanks for the prompt response.
dbrotherLeave a comment:
-
VBA SQL does not accept textbox parameter
Hello all,
I'm requesting assistance with this spot of code. I have a form that I want to calculate the standard cost for our inventory items based on what item is entered into the form's textbox. In the SQL code, I can "hard code" a part number and the SQL query works fine, but when entering the strPartNum variable, it gives me no records returned in my message box @ line #62. Line 14 is where the part number variable is introduced.... -
Yes CurrentProject. Connection is available for Access 2003. I use same method and version of Access, which is called ADO. ADO is Access's (newer versions) default data access method.
I realize you did a quick search and from your initial post, I could tell that you weren't very comfortable with the VBA syntax that is required to do what you want.
I also recommend the 'ACCESS 2003 VBA for dummies' (not saying that...Leave a comment:
-
Firstly, are you sure it's not a permissions issue? The error " you can't go to the specified record" seems like you can't create a new record, which means you don't have access.
You will have to do a good bit of research on ADODB recordsets and connections to do exactly what you want to do, but I will provide some coding of such an example. The process below goes as follows:
1) Just getting a formatted date...Leave a comment:
-
You might want to check to see if it's a permissions issues. Does the account you're logged in as have write access to the SQL server?
I've always found it easier to link the SQL table to your Access program, then make a connection to it using CurrentProject. Connection and modify it as if it were a 'local' table.Leave a comment:
-
Thanks for the quick reply. The vbar is valid in SQL and in Access as I have other queries in other programs that work successfully. The vbar indicates a concatenation. In this example it uses 3 fields seperated by '-' to equal another field in another table. I feel very strongly that this isn't the problem, because I have used it in the past and the compiler runs fine.
The second point you make about the dangling parenthesis...Leave a comment:
-
VBA SQL - Missing right parenthesis error
Hello All,
I've been plugging away at testing some VBA SQL that executes when a user clicks a button from a 'custom query' form. The user has lots of options as to what parameters should be included in the query, so there might be a better way to handle the syntax here. My main concern right now is just possible getting a fresh set of eyes on the VBA to see what I'm missing. I'm using the form's textbox field txtKitNum (strKit)... -
Doh! I new that I was overlooking something easy.
Thanks for the reply.
Working code for random number generator using SQL to get recordset:
...Code:Randomize DoCmd.SetWarnings False Do Until intLoop = UserNum RandNum = Int((HighNum - LowNum + 1) * Rnd + LowNum) strRandNum = RandNum strSQL_LOC = DLookup("LOC", "tblALL_RACK_LOCS",Leave a comment:
-
I'm not seeing the commented line to which you are referring...
There are 3 lines commented in the code that I posted.
2 lines of SQL statement that aren't/shouldn't be used and 1 Debug.Print
If you could reference line number or paste the code line, I would be grateful.
Thanks for the quick response.Leave a comment:
-
Using SQL in Do Until Loop
Access 2003 Win XP Pro SP3
Using SQL /ADO Recordsets in a Do Loop
Hello,
I'm using a random number generator based on an integer input from a user from a form that will get X number of random records from an external Oracle source using a SQL statement. The SQL statement works as expected when the loop code is commented out, but I receive an error "SQL command not properly ended" when the loop is active.... -
VBA MSComm troubles
Hello,
I have a program in Access that uses VBA to capture data being sent over the serial port from a Cosmo Flow Test Machine. This determines the air pressure's leak rate escaping from a muffler.
I have written several programs that send data out serially, but I am new to "listening" to receive the data.
I can setup an instance of HyperTerminal, provided the settings used in the given manual for... -
I was assuming that MSComm was inherent to VBA. It specifically says "Object required", so that would make sense.
How would I go about putting something such as this on a form? MSComm is the way to open the serial port for communcation between devices. It's not like a textbox or button. Maybe I'm a little confused.
What would I need to do to put this onto the form?
Also, could I put this...Leave a comment:
-
VBA RTE 424; Object Required
Hello,
I'm receiving a "424 Run time error: Object required" in the simple program that I've created in Access 2K3 SP2. The program is in a manufacturing environment. Here's an overview of the process: an operator will scan a 2D barcode, that input triggers an engraver to engrave the input onto metal. Then a verification camera takes a picture and verifies the mark to the original data.
I get the error code on the...
No activity results to display
Leave a comment: