Replies

I do have a full version of Adobe, and nope, no dice.

Once the PDF is created, I can manually add bookmarks for where the value in the header section changes, or split the big PDF into multiple PDFs, but I have to believe that some person more clever than I has automated that process and is willing to make a reasonable profit for their efforts.

Thanks, all, for the suggestions. After trying a number of variations in VB.Net on System.Text.Encoding..GetString() with no great luck, I tried re-writing the C# function linked in my first post, but…well, let’s just say it didn’t work. Ultimately, rather than mess with rewriting it in VB.Net, I decided just to include the C# code and call the function from my VB.Net app. How?, you ask. Thusly:

(1) Create a new C# project.
File -> New Project ; Visual C# Projects -> Class Library.
(2) Paste in your borrowed C# code
Sounds easy enough, but if you’re a complete C based novice like me, the first question is, “Where do I paste it?”. You paste it below the opening squiggly bracket of the public class bit.
(3) Add the C# project to your VB.Net solution
Right-click on the VB.Net solution name in the Solution Explorer window and choose Add -> New Project. Browse for the location of your newly created C# project.
(4) Build your C# Project
Right-click on your C# project name in the Solution Explorer window and choose Build.
(5) Reference and call your C# function from VB.Net
Again, sounds simple but if you’ve never done it, here’s how. First, some names you

Going out on a limb: perhaps there is a clustered index on the SQL table, and the addition of a new record somehow confuses your app? When SQL Server uses an index, it finds the storage location of the rows needed by the query and extracts only the needed rows. The addition of a new record would force a reshuffle of the data structure — kind of a SQL based bait and switch.

OK, reading back over my “theory”, it’s waaaay out on a limb!! Good luck!

Nick:

First of all, best of luck!

All of the SQL Server services run under some Windows user account. I’m going to go out on a limb and guess in your case it’s running under the domain Administrator account because that was probably how somebody was logged into the SQL server when SQL was installed. If they don’t want you to have access to that account info ask them to create a domain account that they can grant administrative rights over the server to. In Enterprise Manager, you can change all of the SQL services, MSSQLServer, the SQL Agent, etc., to run under that account. You’ll also want to know the System Administrator, or sa, account info for SQL as well.

For backing up db’s, you’ll also want to make sure your new domain account has necessary rights to write files to some backup location, preferably on another server.

I think that should get you started. Again, best of luck!

I’m glad you found it interesting, Lucas!

So, your users want to see the items in the combo box listed as 1, L1, 2, L2, 3, L3, etc., yes? I’d add a new column to the query that the combo box is based on, and use that column for sorting the combo box. To get it to sort properly, we’ll use the number if it’s a Silent Auction, and extract the number from Ln if it’s a Live Auction. For brevity’s sake, I’m going to assume you know how to edit the query a combo box is based on; if you don’t, post back up.

The new column you’d insert would look like this:
Sort: IIf(Left([ProgramNum],1)=”L”,CDbl(Right([ProgramNum],Len([ProgramNum])-1)),CDbl([ProgramNum]))

Logically, it reads: If the left most character in ProgramNum is a “L”, grab whatever is to the right of the “L”, and convert it to a number, Else, just convert ProgramNum to a number. Sort this new column, which I’ve creatively named “Sort”, ascending. Hide this column in the combo box, make sure your bound column is still correct, and it should work out. I’ve got a dummy table and form in an Access db that I can post up if it’ll help illustrate. Best of luck!

Lucas:

I’m glad you already found the hidden link, so this post is more for reference than anything else. It may be the case that you can’t or don’t want to change the data type for one of the fields you must use as a linked field in your query; it may not be practicable or it’s a linked table, or any of a number of other possibilities. You can get around the “Data type mismatch” error by editing the SQL generated by the query designer.

So, assume you still have your original table design, and you have joined the tables in Design View only to get the dreaded “Data type mismatch” error. No problem. Choose to view the SQL View, and you’ll see something like:

SELECT tbl_Catalog.BidWinnerID, tbl_Bidders.BidderNbr
FROM tbl_Catalog
INNER JOIN tbl_Bidders
ON tbl_Catalog.BidWinnerID = tbl_Bidders.BidderNbr;

You can use the conversion function CLng to transform the value in BidWinnerID on the fly to a number, and that number is used in the join. So you’d edit the SQL to be:

SELECT tbl_Catalog.BidWinnerID, tbl_Bidders.BidderNbr
FROM tbl_Catalog
INNER JOIN tbl_Bidders
ON CLng(tbl_Catalog.BidWinnerID) = tbl_Bidders.BidderNbr;

If you try to switch back to Design View after making the SQL edit, Access will yell at you saying it can’t represent the join graphically. If you’re happy with the results, though, no big whoop. Or simply delete CLng function and Access will display the graphical view again.

Ian:

(a) Ignoring the dialogue box may make it difficult or impossible to run an update query against the linked table. However, it sounds as though you’re only pulling information from the SQL tables for reports, so probably not a great concern there. I’ve not run any tests to see if there is a performance hit, though, for not properly indentifying the key fields — there may well be.
( Again, I don’t think much would happen if you’re not running update or delete queries against the linked SQL tables.

While for your purposes I don’t think ID’ing the key fields in the linked tables is essential, I find it helpful when designing queries against linked tables, no matter the data source, and for being able to reverse engineer just what the heck I was thinking when I revisit projects months later! Best of luck!

Thanks, Andrew! This thread gave me exactly the solution I was looking for.

This post may be a bit of a waste of bandwidth, but I think it’s important to note the usefulness of the Lounge’s Search function.There are tons of posts addressing problems of Loungers past and present.

Cheers!

Adding more RAM to the box never hurts!

Without taking it too much off topic for the Access forum, if you can, archive some of those records in another SQL database dedicated to reporting. This would let you create indexes on the tables that will speed up queries; indexes of that nature might well be improper to have in a database that’s seeing 100k new records added each day, i.e. the difference between an OLTP and and OLAP database. If you can’t archive some records, see what you can do about creating/modifying indexes on the tables, or as I mentioned before, perhaps you can move that query into a stored procedure.

Finally, you might fire up SQL Query Analyzer. Paste in your SQL, and check out the execution plan. This will tell you which bits are consuming the most resources, and might point you to some trouble spots you can smooth out by reworking the query or the structure/indexes/etc. of the underlying tables.

Good luck, Mike!

Hmmmm…I don’t know exactly how in the list of values the O/S determines whether it’s a System or User DSN. I do know that the following text, pasted into a file with a .vbs extension, will create a System DSN for a SQL Server connection named MyDSN on a Windows 2000 and XP box, though.

Dim oWshShell
Const cRegKey1 = "HKLMSoftwareODBCODBC.INIMyDSN"
Const cRegKey2 = "HKLMSoftwareODBCODBC.INIODBC Data Sources"

Set oWshShell = CreateObject ("WScript.Shell")

oWshShell.RegWrite cRegKey1 & "Driver","C:WINNTSystem32sqlsrv32.dll"
oWshShell.RegWrite cRegKey1 & "Server","Insert name of SQL Server here"
oWshShell.RegWrite cRegKey1 & "Database","Insert database name here"
oWshShell.RegWrite cRegKey1 & "Description","The text description you see in snap-in"
oWshShell.RegWrite cRegKey1 & "Trusted_Connection","Yes"

oWshShell.RegWrite cRegKey2 & "MyDSN","SQL Server"

set oWshShell = Nothing 

Interesting problem, Mike! The 30 second time-out may be telling; in the ADP, choose Tools => Options, and head to the Advanced tab. The OLE/DDE timeout is defaulted at 30 seconds. Assuming that solves the problem in your ADP, or at least creates another one, it doesn’t explain why running the SQL statement on the server directly, through Query Analyzer?, gives you the same issue.

30M records is a bunch. You may want to check that you have efficient indexes set on the underlying tables to help the query engine, and you might consider getting that SQL into a stored procedure. The execution plan for a stored proc is compiled and will generally yield results faster than the same SQL run as a view. Good luck!

I’ve not had any programmatic experience with CR; only as an end user, and supporting other end users, for other people’s products. In that capacity, I’ve never, and I mean never, seen a stable, user friendly implementation of Crystal Reports. Granted, it may be that I’ve just never come across a product whose development team was up to the task, but…

*shudder* Crystal Reports.

This might be a bit kludgy, but….

You’d need to create:
(1) A NewPlants table in your Access db
(2) A stored procedure in your SQL db that accepts input parameters of Plant ID, Plant Name, Plant Address and so forth. This stored proc then appends the record to the SQL Server Plants table.
(3) VBA code that (a) connects to the SQL db, ( loops through the Access NewPlants table and for each record it finds passes the necessary info to the stored proc © closes the connection upon completion, (d) deletes records from the NewPlants Access table.

As you add new plants to your Access Plants table, they also get written to the NewPlants table. You don’t have broadband, so when you have some free time on your dial up connection, you launch the process that calls the VBA you wrote for step 3. Hmmm…did I say a bit kludgy?!

I think you’ll be better off following the path that Mark and Wendell are outlining for you. Also, I’d suggest that if Office Developer is out of the ball park, financially speaking, licensing SQL Server for a production commercial application is, like, waaaaaay out of the park! By the way, MSDE, the SQL Lite that ships with .NET, isn’t allowed for production databases, and caps use to only 5 concurrent connections.

Good luck!

Sarah:

I’ve not run into that particular error, and so can’t offer any advice on how to overcome it. However, I can perhaps help you achieve your goal. I’d say your two best options are to:

(1) Go though the File -> Import and Export… menu option in Outlook, but export to a common third format, say Excel or a comma separated values file, then import that resulting file into Access.
(2) Link directly to the Contacts list you’re concerned with. In Access, choose to link a new table. When prompted for the file, look in the Files of type… drop box for “Exchange()”. Once you choose that option, a Link Exchange/Outlook Wizard will fire up, and you can surf to link to the contact list you wish to have.

Good luck!