Search results

I would bring the client records into a new database. Allow the new client table to assign a new primary key (autonumber). Additionally, bring in the old primary key, and stick it in another ordinary (not autonumber) int column. Do one database at a time, since you have duplicate ID's. Next...

You could upsize it to a hosted SQL Server. Worked for me. I have users in Europe and the US and it's working great. Downside is if you don't know SQL Server the learning curve is real.

Glad problem solved, my first thought reading this before I saw the primary key missing was blocking. It happens in SQL Server, and you have to intervene through SSMS to undo it. Rare, but it does happen. FYI.

I use this algorithm to get the previous ID of a table to build rolling reports. You could change the WHERE clause to "= ID + n" where n is how many subsequent rows you want to go. This assumes a unique Identity (autonumber) column. This might be a start anyway. SELECT ID AS AHID...

Can you take a pass through the data first, and replace the - with a : ?

I had the same problem, my remote users were in the wilderness counting fish and measuring habitat. They took an Access app with local tables out there, collected the data, and then handed them off to me. I was lucky to be using SQL Server (way too much data for Access) so I could build, save...

I agree with Lightwave on this one. And I'd go further - for an order I'd store the order total (derived from summing the order items prices). I would also store the customer name, ship-to address and so forth in the order table. Customers change their names by marrying, their address changes...

You can make a fabricated recordset. It has no connection, is not bound to a table. Dim rs As New ADODB.Recordset rs.Fields.Append "ID", adBigInt rs.Fields.Append "Name", adBSTR rs.Open rs.AddNew rs!ID = 1 rs!Name = "Bob" rs.MoveFirst MsgBox rs!Name rs.AddNew...

Using the pear example above, say you table is storing the ID (3) of the pear in a column named FruitID. You'd have to populate the parameter with the numeric (3) to return that row. You could denormalize the relationship by creating a query that contained the literal 'Pear' values and the...

Have you considered creating a parameterized stored procedure? That's how I work with SQL Server from Access, and a set-based solution is much faster than looping through a recordset. If you can do that (I understand that sometimes the SQL Servers are controlled by DBA's that won't let you...

You are correct. Make it a correlated subquery with a WHERE clause relating it to each book. Also, there need to be an ISDATE clause in there to exclude rows with no dates.

If the most recent record has a date column, you can use the TOP keyword with an ORDER BY to select just the most recent record in one SQL Statement, like this: Table: ID ReturnDate BookName For this table, if the same bookname was returned sever times on several different dates, to get just...

Yes. There should be an OrderItems table. Then you can n items per order, not just three. Products table should lose the Profit column. That's derived data. There should be a ProductPrices table - different customer types get different prices, and marketing campaigns often offer special pricing...

I started my current project in Access 2007 and moved to Access 2010. I like to select a table, and click "Form" under the Create tab on the ribbon. I get a form and I can then modify it, but I've skipped adding all the bound fields. A time saver. My problem is that, even in Access 2010, on...

I'm developing against SQL Server 2008 with Access 2010. I believe ADPs are deprecated. My setup is Windows Authentication on the SQL Server (best practice), and linked tables (and views, since Access treats lined views as tables) via ODBC on the front end. One advantage of using linked tables...

I just had to solve that problem last night, although in my case I have to create n rows based on the existence of several rows in a lookup table. Here's my code. I'm using SQL Server so I call a stored procedure to do the actual insert, which I'll include below for completeness. For Access...

Remoted into the new machine and manually re-linked the views (and a couple of tables as well), now it all works. I'll have to write some code to do that, but that's no problem.

Hello, First, thanks to all who try to help in this forum. This is a very civilized place, compared to others of it's kind. I'm using Access 2007 against SQL Server Express 2008 R2 with linked tables. Works great, and it's not an ADP. I installed SQL Express on a new machine. Restored the...