Search results

My guess is that you did something wrong with the BCP settings. You can set the number of records per batch higher or lower. (Higher will speed it up, but it'll require more memory.) Did you read up on all the switches?

if it's confidential data, I don't think I can help. but if it's not, you could send me a message with one or two PDFs (oh, and the expected output... could be an explanation), I could see if PowerQuery would work any better.

(The point being that they're a trap. What you see is not what you get [stored]). I remember warnings to this effect on a website that's been pretty much dead for 20+ years. The junk that gets put in Access is seriously insane.

Oh, I know!!! Make an account on UtterAccess and ask Volcanics. He uses them all the time!!!

Is this the classic Students and Classes database? If it is, this smells an awful lot like homework. (I remember building this database at university). If this is for class, you really need to figure this out for yourself, because the whole point of this exercise is understanding table design...

Okay, I get it now. No wonder you're so confused! (I would be too!). Here's the problem - to get this to work in your database, you need a different structure. You need basically just two columns, one for PartNumber and the other for ModelNumber. Do you get tons of these files basically in this...

Then couldn't you just tack the insert part onto the front of that? (yes, you need to fill out all the column names in the mysterious "MyTable" and then you just glue them together... insert_sql = "INSERT INTO MyTable(lnquiryID, InquiryDate, InquiredBy,....) " CreateSQL = insert_sql & " " &...

for example?

If they're SQL Server and you have not added any indexes, then performance can be utterly brutal. I had a table once that had maybe 160K records and performance was terrible. So which is it? SQL Server or Access? If the table comes in with a prefix (usually dbo.) then that's SQL Server (it's the...

What if you create a new blank database and import just the 3 tables and the minimum number of queries and forms for us to see the problem? Surely we don't need the entire database.

Oh, okay. This is what I would do... use Excel. Data > Get Data > PDF... then you'll be prompted for a file. The hard part is when PowerQuery cannot easily parse the table. That's not to say that you can't use PowerQuery to do it, but it takes some knowledge of M to do it. The reason I would use...

I'm not sure if Access sneakily adds indexes to columns ending in "ID" etc, but I know for sure that SQL Server does absolutely no indexing on its own. If you want an index somewhere, you have to create one. The one thing your post is missing that would be really helpful is the CREATE TABLE...

CJ, if (and it's a big if!!) your PDF contains tables, and you want only them, then PowerQuery is great. If you want something else, then not so much. PowerQuery basically only reads the tables and then removes all the formatting (or tries to). If you've used Get Data in Excel, you've used...

Excel Doc where? Did I miss it?

I could be wrong, but it sounds like you should be using a combobox with LimitToList set to True and getting the available values from a table. Your solution seems to be to correct the problem after it's happened instead of preventing it from happening in the first place. "make the data entry...

One thing you can do that's super easy is to create a linked table and connect to one of the CSV files. You'll have to define data types, but then you can just query and append etc. Doing this manually sounds really painful. Post some sample data (doesn't have to be real... I don't want...

What do your CSV files look like (what's the structure?) How many files you do you need to import and how often? Assuming you have a "ModelParts" table that consists of (ModelNo, PartNo) at least and maybe some descriptive attributes (like Quantity), then your tables would be Model(ModelNo...

If you put that in Excel, you can unpivot it and you would end up with (ModelNo, PartNo), which would be useful. Then that's your junction table. Ideally, it would include a Quantity of each PartNo for each model, so it would be (ModelNo, PartNo, Quantity) Part #s have a UID, Part #...

How are Parts and Models related in the real world? A Model consists of one or more Parts? Model--(1,M)--ModelParts--(M,1)--Part Say the primary key of Model is ModelKey and it's a unique integer. And Part's Primary Key is PartKey. So, by definition, PartKey and ModelKey are both unique. Then...