Code:
Dim db As DAO.TableDef
Dim newbuild As String
newbuild = InputBox("Please enter the database build:")
For Each db In CurrentDb.TableDefs
If Len(db.Connect) > 0 Then
Himmel
Last Activity: Aug 8 '07, 04:32 PM
Joined: Apr 18 '07
Location:
-
Found it myself. :) In case anyone else wants to know how, here ya go. I go through ODBC so I've included the ODBC reference info. I believe you can leave it off, but I'd rather not chance it. :)
... -
Change the Database that a linked table references
Hello,
I am using an access database to query a very large number of linked tables. Currently, the name of the database the linked tables point to is always the same name. However we are switching to a new system that uses versioned databases. Each time a new build of the database is published, a whole new database is created.
For example, we are currently on version 1847. The linked tables are set to look in "Database_1847" .... -
And I answered my own question. :)
I was able to resolve the problem by encapsulating the rstmp data with a WITH statement, and ending with a .CLOSE
...Code:set rstemp = dbo.OpenRecordSet("SELECT * FROM NEWTABLE WHERE ID=" & rs![ID]) With rstemp For Each FLD in rstemp.Fields strname = "OBJ" & r if FLD.Name = strname thenLeave a comment:
-
I've discovered that if I remove the rstemp code:
...Code:set rstemp = dbo.OpenRecordSet("SELECT * FROM NEWTABLE WHERE ID=" & rs![ID]) For Each FLD in rstemp.Fields strname = "OBJ" & r if FLD.Name = strname then r = r + 1 if Len(FLD.Value) > 0 then z = z + 1 End if End if Next set rstemp = NothingLeave a comment:
-
The Database Engine Could Not Lock Table error in Access 2003
I have added a section of code to a function that is designed to copy data from several tables and place them into a single table. The new table already contains a unique ID and name, and I am working on adding data from the second table to it.
The problem is that the second table has multiple records for each ID. For example, review this sample data:
New Table:
...Code:ID NAME OBJ1 1 Billy 2 Bobby
-
How to write query results to a new table with Access 2003
Hello!
The reference database I currently use runs queries that pull data from hundreds of tables in order to create user-friendly form view. The problem is that these queries can take upwards of 30 seconds to return.
The solution that I've devised is to create an admin function to cache all these query results and then dump that data into a new, consolidated table, and then have the users search through those new tables.... -
I took a page out of your book:
This handles fields that have an apostrophe. But if any have a quotation, it breaks....Code:DB.Execute "INSERT INTO NEWTABLE (Record1) VALUES(" & chr$(34) & NZ(rs![oldtext],"") & chr$(34) & ")"Leave a comment:
-
Thanks guys. :) This is starting to shape up nicely.
I hate to throw a wrench into the works, but now I'm getting an operand error when the SQL encounters a record that contains a quotation mark. And again, the field may be Null.
Any suggestions on what to do next?Leave a comment:
-
That returns an error on the first value it tries to write: "Syntax error (missing operator) in query expression."
In Debug, I can hover over the expression it is saying is in error, and it (along with all other expressions) contain the data that is expected.Leave a comment:
-
Replacing apostrophe in a recordset for SQL insert
Hello!
I am attempting to take data from one table and insert it into another. I am encountering problems where data in a recordset may contain an apostrophe, which completely kills my SQL Insert.
Problem is, some of these records may also be Null, so that kills a Replace() call.Code:DB.Execute "INSERT INTO NEWTABLE (Record1) VALUES('" & rs![oldtext] & "')"
I've... -
Pulling record info from a tabular form in Access 2003
I may be brain-fried, but I could use some help again.
One of my glorious forms in my multi-form report thingiemajigger is a tabular form. For example, it shows customer items purchased and the price.
"Item1" "1.99"
"Item2" "2.99"
I'd like to pull that data into a single string from another form. But my variable assignment just keeps pulling the first item... -
How did you know? :-)
A million thanks. Good god I've been staring at this thing waaaay too long.Leave a comment:
-
Yeah, appears to be a problem with having a tabs control. Do I need to refer to the tabs control and/or tab the second form is on?
I'm confusing even myself, so hopefully a screenshot will help illustrate this. :)
...Leave a comment:
-
Actually, I tried this in a test form (parent form with two child forms on it) and it worked fine. However, on the live form (using tabs control) it does not work.
I get "Applicatio n-defined or Object-defined error"...Leave a comment:
-
Thanks Mary,
Yup just figured this out myself, about 10 seconds ago. lol.
I was trying to derive the proper object properties. My ! and . and syntax were tripping over each other. haha.
"msgbox(Forms!m ainform.form2!b alanceDue.Value )" did the trick. I can stop bashing my head into the table now. haha...Leave a comment:
-
Pulling data across multiple forms in Access 2003
Hello,
I have a database form that has a tabs control, and on each tab is another form. Each form pulls data from a different query.
For example, tab 1 may list personal information (form1), tab 2 shows billing information (form2).
I have a rather simple problem, but I can't seem to figure out how to pull data from the form on tab 2 and display it on tab 1. So if the form2 on tab 2 has a field called... -
Thanks. I will put a limiter in so it won't keep breaking....Leave a comment:
-
Come to think of it, perhaps you could bring your considerable expertise to review this code and let me know if it's optimal. :) This uses some integers that are based specifically on the gaming environment, so some of the static declarations (range, imagemap, worldmap, etc) are set that way for a reason.
...Code:Public Function TemplateForm() As String Dim rst As ADODB.Recordset Dim ctl As Control Dim
Leave a comment:
-
That's the info I was looking for. :)
A new problem though... Is there a way to make Access create more than 878 controls on a form? This is a monster of a game and each area I want to display has at least 1000 NPC's in it.Leave a comment:
-
Well, the idea is to dynamically place pins after a query. So for example, I search for everyone in "room 1", and there are 5 people. So I want 5 pins to be placed. When I hit "search" a new form is opened to display the image of the room. So having code that modifies the form at a design level should not cause any problems. Or if there is a method to create a dynamic form, that would work too.
I hope I'm making...Leave a comment:
No activity results to display
Leave a comment: