Is there any limit of records?

[setis]

I have an excel linked to my database to auto-populate several fields.
This excel is dynamic and gets auto-updated from our navision system.

This excel has around 31.500 rows and we have experienced that the visibility for ALL records is intermittent; I could see some records that my colleague could not and when I closed and opened. The same row wasn't visible for me anymore.

Does anyone have had experience managing large number or records like this and is it a more stable way to have access to all these records coming from Navision (MS CRM) than a linked dynamic excel sheet?

Thanks in advance

 

[Minty]

I would have a read here https://forum.mibuso.com/discussion/29748/connect-ms-access-to-navision-for-dummies

It appears that you can have a SQL server or Native Server version of Navision, and therefore there are different methods.

 

[Ranman256]

make sure your PC has LOTS o memory.

 

[Pat Hartman]

Sounds like you are linking to Excel from Access. The fact that the Excel originated as an export from Navision (whatever that is) is irrelevant.

If people are using TransferSpreadsheet to link to or import data from Excel, make sure they are selecting the correct Excel version. acSpreadsheetTypeExcel12Xml is the version you need to select if you are importing .xlsx files.

EDIT -- I forgot to mention that using the correct SpreadsheetType will give you all the rows in the spreadsheet. If you use an older type, you will be limited to whatever rowcount that version of Excel supported.

 

[setis]

Sounds like you are linking to Excel from Access. The fact that the Excel originated as an export from Navision (whatever that is) is irrelevant.

If people are using TransferSpreadsheet to link to or import data from Excel, make sure they are selecting the correct Excel version. acSpreadsheetTypeExcel12Xml is the version you need to select if you are importing .xlsx files.

Thanks a lot for this

 

[Pat Hartman]

You're welcome

 

[setis]

Sounds like you are linking to Excel from Access. The fact that the Excel originated as an export from Navision (whatever that is) is irrelevant.

If people are using TransferSpreadsheet to link to or import data from Excel, make sure they are selecting the correct Excel version. acSpreadsheetTypeExcel12Xml is the version you need to select if you are importing .xlsx files.

EDIT -- I forgot to mention that using the correct SpreadsheetType will give you all the rows in the spreadsheet. If you use an older type, you will be limited to whatever rowcount that version of Excel supported.

I've looked at the TransferSpreadsheet method and I'm willing to try that.
The thing is that this big table with >32K rows is now a linked table and the weird thing is that when I open the excel itself, all the records are there, but when I open the linked table from access, there are a bunch missing.

Would it be better to transfer the spreadsheet to a table and trying to append the new records when the the original one gets updated?

 

[Minty]

From the links I provided I believing you can link directly to the original tables/server.

No need to involve Excel from what I can see.

 

[isladogs]

I've looked at the TransferSpreadsheet method and I'm willing to try that.
The thing is that this big table with >32K rows is now a linked table and the weird thing is that when I open the excel itself, all the records are there, but when I open the linked table from access, there are a bunch missing.

Would it be better to transfer the spreadsheet to a table and trying to append the new records when the the original one gets updated?

Yes it would be better to import the excel file to an Access table particulary if there are several users and a shared backend db.
Performance should be better plus if needed the table will be editable in Access whereas the Excel file cannot be edited from Access.

You could set up a routine to run the import automatically at times of your choice e.g. overnight

 

[setis]

You could set up a routine to run the import automatically at times of your choice e.g. overnight

Thanks a lot for your answer. Could you please point me out to an information source to set this up? This would be great!

 

[arnelgp]

the problem that you will be facing
when importing the excel file to a table
is identifying which records already exists
in the imported table, when its time to
re-import again, since the workbook is dynamic,
maybe there are deletion and additions of
rows on the workbook.

 

[setis]

the problem that you will be facing
when importing the excel file to a table
is identifying which records already exists
in the imported table, when its time to
re-import again, since the workbook is dynamic,
maybe there are deletion and additions of
rows on the workbook.

Yes, that's why linking the dynamic excel sheet sounded like the right thing to do here.
I can imagine that setting up a process to append new records to an access table from a spreadsheet that it's continuously updating can be a nightmare.

 

[Gasman]

I reference an Excel workbook in one of my databases and the workbook is then read only to Excel users. Fortunately that is only me, but something to be aware of.?

Yes, that's why linking the dynamic excel sheet sounded like the right thing to do here.
I can imagine that setting up a process to append new records to an access table from a spreadsheet that it's continuously updating can be a nightmare.

 

[isladogs]

Thanks a lot for your answer. Could you please point me out to an information source to set this up? This would be great!

Out at the moment and answering on my phone. If you create a routine to import the excel data to an access table I'll reply again this evening telling you how to automate it using task scheduler


Sent from my iPhone using Tapatalk

 

[isladogs]

Yes, that's why linking the dynamic excel sheet sounded like the right thing to do here.
I can imagine that setting up a process to append new records to an access table from a spreadsheet that it's continuously updating can be a nightmare.

No it isn't.
You need 3 queries or equivalent in VBA.
1. Append new records where these don't already exist.
2. Update existing records where changes have occurred
3. Delete existing records which aren't in the Excel import ...
OR mark them as inactive if preferred.

If you want me to explain how to do this automatically using task scheduler, let me know

 

[arnelgp]

Please tell us, how will you manage duplication, deletion an insertion of rows in excel. Pk on all field in the table will not work if there are identical rows in excel entered in two rows.

 

[isladogs]

Basically you need a unique field in the Excel table so you aren't relying on an autonumber PK field.

For example with my schools databases, I run this process over night every night. I use unique pupil number (UPN) as the reference field.

I extract 30 CSV files remotely from the schools management system database and import each of these to temporary 'buffer' tables so I can modify where necessary.
I then run procedures to do the append/update/delete on each file.

The whole process is done at a set time overnight using task scheduler.
For simplicity, I have a separate utility database to run this process.
It is linked to both the CSV files (or Excel files in this case) and the destination db.
When it runs, it first locks the main database to prevent possible data conflicts and when complete it unlocks it again.
It also creates a log file of each part of the process and emails that to a designated email address when complete.

 

[setis]

No it isn't.
You need 3 queries or equivalent in VBA.
1. Append new records where these don't already exist.
2. Update existing records where changes have occurred
3. Delete existing records which aren't in the Excel import ...
OR mark them as inactive if preferred.

If you want me to explain how to do this automatically using task scheduler, let me know

I would actually be more than grateful if you could explain me how to do this. There is no PKs in the dynamic spreadsheet, since this is only used as a lookup to bring a "Case Number" field on a form and auto populate some other fields in the form.

Thanks so much!

 

[setis]

Before throwing myself into Ridder's solution, I tried to do a work around with my linked dynamic spreadsheet.

One problem that I had with Microsoft CRM was that I was only allowed to export 10.000 records at a time. What I had to do was to export from the selected date 6 months at a time and pasting the results into one spreadsheet. The last bit was a dynamic spreadsheet exporting the cases created on or after the 25/10/2017.
Then, I pasted all the results from the static worksheet into the dynamic one and this works actually great on excel. The spreadsheet refreshes correctly and the new cases appears there.

Linking this spreadsheet in access is problematic because I can see all records except those created on or after the 25/10/2017. I still don't get why, since I can see them on the excel.

Using the information here (second option) https://dynamicscrmpros.com/export-limitation-microsoft-dynamics-crm-microsoft-excel/ I could bypass the 10.000 records export limit and create a dynamic spreadsheet with all the 32K records BUT, linking this one to access shows me 0 records. It shows the records in the count below, but the rows are empty.
There must be some kind of problem linking to dynamic spreadsheets like this.

 

[Pat Hartman]

The thing is that this big table with >32K rows is now a linked table and the weird thing is that when I open the excel itself, all the records are there, but when I open the linked table from access, there are a bunch missing.

Keep in mind that queries (and tables) are unordered sets so unless you sort the data on a unique value, you may get inconsistent results. In this case, it is likely that at some point in time the Excel sheet was sorted so what you are seeing is the result of that rather than the original physical order. Access would be seeing the original physical order. Check the record counts to confirm.

I have Access apps that import/export a million rows from/to Excel so 32k is peanuts.