COMPGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Access 10 (64bit) -Integer & Table size

• Email
• Follow

I have a potential need to use integer numbers up to 17 digits long.
Access03 that I currently use can go up to 10 digits (32 bit
restriction). I am wondering whether Access10 (64bit) woould solve the
problem. I understand that it has three new keywords (2 data types and
1 modifier): LongPtr, LongLong and PtrSafe, a new function: CLngLng()
and new compilation constants: VBA7 and Win64.

What is the biggest integer value that Access10 (64bit) can support?

Also what is the maximum physical table size that Access10 (64bit) can
support?

Any caveats on using Access 10 Vs Access 03 would be appreciated.

John
 0

See related articles to this posting

?"JohnP"  wrote in message=20

>I have a potential need to use integer numbers up to 17 digits long.
>Access03 that I currently use can go up to 10 digits (32 bit
>restriction). I am wondering whether Access10 (64bit) woould solve the
>problem. I understand that it has three new keywords (2 data types and
>1 modifier): LongPtr, LongLong and PtrSafe, a new function: CLngLng()
>and new compilation constants: VBA7 and Win64.

There also a function called CLngPtr to convert expressions to a long=20
pointer data type.

As a minor note, the numbering system in versions of access is:

access 97            access 7
access 2000        access 8
access 2002        access 10   (this version was also called XP in some=20
cases)
access 2003        access 11
access 2007        access 12
access 2010        access 14

Note that 13 was skipped.

So, when you say access 10, it generally accepted to mean access 2002 =
not=20
2010.

So, it is best say access 2010, or access 14, but not access 10 when you =

mean to say 2010.
(as this as noted this is minor here, but wrongly using the version =
number=20
could become confusing in the future for you).

ok, lets tackle some questions:

>What is the biggest integer value that Access10 (64bit) can support?

It a real 64 bit sighed big honking integer. It is 8 bytes in size. The=20
range is:

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

So, it can handle up to 19 digits as per above.

However, this will NOT help you, since in a table, the long data type =
HAS=20
NOT changed, and remains a 32 bit long.

What I would suggest in this case is to simply use a decimal column. =
There=20
are some noteworthy problems, but those columns can easily handle 17 =
digits.=20
in fact, the default is 18 digits without a decimal place. And the real =
nice=20
feature about decimal collums is they don't need the 64 bit version of=20
access. In fact, I think they go all the way back to access 2002, or =
even=20
2000.

So, in table design mode, choose number type, and then in the drop down, =
you=20
will see the decimal type as a choice. When you choose it, you see a =
option=20
for digits (it defaults to 18, and for scale, you see 0).  I would use =
the=20
default of 18 and 0 for decimal places.

Note that if you need to use this number in any VBA code, you have to =
use a=20
type variant.

You are thus best to use the above data type, and it will work in pre =
2010=20
versions (you do not need 64 bit edition to use this feature).

>Also what is the maximum physical table size that Access10 (64bit) can
support?

The allowed size of 2 gig has not been changed.  So a typical mailing=20

2 gig =3D  2,147,483,648 Bytes
1 Character =3D 1 Byte
120 Characters =3D 120 Bytes

2 gig / 120 =3D 32 million records

So, the limit is NOT the number of records..but "how many" records you =
can
fit into 2 gig.

Of course, if each customer name only takes up 60 characters...then you =
can
store 64 million records in the file.

So keep in mind that data types and size for tables are
not changed, even when running in 64 bit version of Access 2010.

I think a decimal type column should work for your needs.

--=20
Albert D. Kallal (Access MVP)

 0

On Dec 19, 4:43=A0pm, JohnP <j...@kosmosbusiness.com> wrote:
> I have a potential need to use integer numbers up to 17 digits long.
> Access03 that I currently use can go up to 10 digits (32 bit
> restriction). I am wondering whether Access10 (64bit) woould solve the
> problem. I understand that it has three new keywords (2 data types and
> 1 modifier): LongPtr, LongLong and PtrSafe, a new function: CLngLng()
> and new compilation constants: VBA7 and Win64.
>
> What is the biggest integer value that Access10 (64bit) can support?
>
> Also what is the maximum physical table size that Access10 (64bit) can
> support?
>
> Any caveats on using Access 10 Vs Access 03 would be appreciated.
>
> John

Hi John,

I do no know what kind of calculus you want to do. But as you are
talking about very long integer numbers, you could also consider
string calculations.
In that case you can store the number up to 255 characters in a text
field, and even further if you use a memo field.

Imb
 0

"Albert D. Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in

> What I would suggest in this case is to simply use a decimal
> column. There are some noteworthy problems, but those columns can
> easily handle 17 digits. in fact, the default is 18 digits without
> a decimal place. And the real nice feature about decimal collums
> is they don't need the 64 bit version of access. In fact, I think
> they go all the way back to access 2002, or even 2000.

Why not just use doubles, which have been available in all versions
of Access I've ever used, starting with Access 2? There are still
problems with the decimal data type within Access -- its
It got properly implemented in Jet 4, and in ADO, but never really
in Access at all, and perhaps not at all in DAO (not sure on that
latter).

http://allenbrowne.com/bug-08.html

--
David W. Fenton                  http://www.dfenton.com/
contact via website only     http://www.dfenton.com/DFA/
 0

?"David-W-Fenton"  wrote in message=20
news:Xns9E54D25BFD927f99a49ed1d0c49c5bbb2@74.209.136.95...

>Why not just use doubles, which have been available in all versions
>of Access I've ever used, starting with Access 2?

The problem here is the OP needs 17 digits. A double will not get you =
that=20
many digits.
(And, since that is floating point then it subject to rounding and you=20
really do not want that)

A  currency data type comes close to the OP requirement and has up to 15 =

digits, and another 4 to the right (so a scaled currency value might =
work).

>There are still
>problems with the decimal data type within Access -- its

>http://allenbrowne.com/bug-08.html

The issues tend to be rather minor in most cases.

The packed decimal is REALLY NICE feature to have and gets one out of =
FAR=20
MORE trouble then it gets you into.

I find packed decimals work well if you know what to avoid. As noted, =
from=20
2003 onwards they work better, and even 2007 shows improvements in =
removing=20
minor nags.

Having written several payroll systems (one even in Pascal from =
scratch), I=20
can assure you that packed decimal support on those systems was a gift=20
horse.

The same goes for WHEN you need them in access. The ease at which this=20
poster can start using 17 digit numbers is at one fingertips with the =
mere=20
creating
of the required column in the table. For most financial work, currency =
in=20
access is the real ticket.

However, for numbers beyond double (about 14 or 15 digits), and beyond =
that=20
of currency, then decimal is a viable and reasonable choice.

As noted, there is some minor issues, but for the most part when you =
need to=20
use large scale (pun intended!) integers, then the packed decimal is a =
great=20
choice IMHO.

--=20
Albert D. Kallal (Access MVP)

 0

"Albert D. Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in

> As noted, there is some minor issues, but for the most part when
> you need to use large scale (pun intended!) integers, then the
> packed decimal is a great choice IMHO.

Or, you need a different database engine.

--
David W. Fenton                  http://www.dfenton.com/
contact via website only     http://www.dfenton.com/DFA/
 0

Access Data Projects can use 64 bit Integers without any trouble whatsoever.

Jet just sucks.. it doesn't support basic datatypes like BIGINT.

 0
Reply aaron.kempf (199) 1/2/2011 9:51:06 PM

6 Replies
753 Views

Similar Articles

12/19/2013 9:45:50 PM
page loaded in 3103 ms. (0)

Similar Artilces:

Question on how updates to Access tables may or may not cause the database to expand in size
See if I have this right: For this I will ignore BLOBS and MEMO fields which I do not use in this application. Data is stored by jet in 2k pages. Multiple records may be stored in a single pages but records are not spanned across two pages. All strings are stored as variable length data, one byte for each actual character and one overhead byte. This is regardless of the string size specified. All other data types are fixed length in size. When a database is compacted and repaired, tables are written out in primary key sequence. I have concluded that after the compact/repair, that...

SQL for Quering Access Tables using a Table/Field List Table
I have a Table called TableField that has the table names, fields and Data type of all the tables and fields in the database. They are set up as follows: Table: TableField TableName FieldName Type Table1 LName Text Table1 FName Text Table1 Address1 Text Table1 Address2 Text Table1 State Text Table1 Zip Text Table2 Color Text Table2 Descrip Text Table2 Size Text Table2 Temp Text . . Table65 ... Not having much Access experience, If I were looking for a specific value "e.g., Orange" located in one o...

Linking an Oracle table to MS Access via ODBC: Access assigns wrong primary key to linked table
Hello, I'm using Access to link to tables of a calculation tool that's been built = on a Oracle back end. I recently updated that tool, and now it seems something has been changed i= n Oracle that causes Access to automatically assign the wrong primary keys = to certain tables. This happens when I update the tables using the connecti= vity wizard or when re-linking the tables. Is there a way to prevent Acces to automatically put keys to the Oracle tab= les I'm trying to link using ODBC? If I'm linking MySQL tables the ODBC con= nection wizard of Access always asks ...

Update Access Table with Excel Table
I have a table that is being utilized in an Access database. There is a number of updates that need to be made on a monthly basis. The updates are sent in Excel format. Is there a way that I can upload the information from the Excel sheet into the Access table? I tried importing it and it won't let me do that because the it can't override the information in the table. I tried linking the database to an excel spreadsheet, but that slows the database down significantly and removes the ability of multiple people accessing the database at the same time. Preferably I would like to be a...

sql size vs. access size
I have a SQL Server database that is showing 2853.44 mb in size but when I export the data into MS Access the size is less than 1 mb. Can anyone tell me how to reduce the size of my SQL Server database so that it's less than 15 mb? Thanks in advance! Rob "Rob Wahmann" <dotcomstudio@sbcglobal.net> wrote in message news:LidUa.25515\$BM.8256484@newssrv26.news.prodigy.com... > I have a SQL Server database that is showing 2853.44 mb in size but when I > export the data into MS Access the size is less than 1 mb. Can anyone tell > me how to reduce the size of my SQL ...

Accessing Oracle Tables from MS Access
How difficult is this? I'm having a tough time getting a simple select to work. I am using MS Access 2002 and Windows XP. What do I have to do to make a simple select statement like this work: select id from emp I have stumbled upon many websites although none straight to the point. Any help in how to get this done, or pointing me in the right direction of a book or website, would be greatly appreciated! berj Yes, I do have the table links working. But I did want to run the select for such things as grabbing the next number off of a sequence. As in: select test.nextval fro...

Barcode Generator for Microsoft Access 10.10
This professional-grade product provides dynamic barcoding capability to Microsoft Access Reports without the installation of additional fonts or other components. Once installed, the product remains embedded in the database, even when distributed. Complete VBA source code is provided with purchase. Supported symbologies include Code 3 of 9, Code 128, MSI, UCC/EAN-128, Interleaved 2 of 5, PostNet, OneCode and PDF417. The demo version is fully functional but does not include the source. More Info: http://idautomation.com/access/native/ Download URL: http://www.advancemeants.com/downl...

How to import/mpa excel table to access table????
Hi. How do I import while mapping an excel table to an access table please??? I've searched around and all I can find is a software product or code that does the same thing as the access wizard..... If I have to write vb code, where is a sample??? Or do I import the excel in to a new spreadsheet and then write some kind of querey to move the data from that table to my current production table.. How do I do this? Seems to me, access would be able to do this??? Thanks, ANY infor would be appreciated.! Fred -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Access can do that. 1. Open t...

AddNew from table A into table B
I am running this on a Pocket PC NOT on a PC. I want to select a row of data, then insert the row of data from tableA into tableB. Is this possible??? Any assistance would be appreciated as I have got stuck!!! Cheers Clive Private Sub Test_Click() Dim RS2 As ADOCE.Recordset Dim SQL As String Dim InputText As String Dim ColumnNames As String Dim ColumnValues As String Dim ParamName As String Dim MyVar As String Set RS2 = CreateObject("ADOCE.Recordset.3.1") RS2.Open Test, "\My Documents\Housing.cdb" ' UPDATE test INNER JOIN HousingEstates ON (test.BlockName = Housing...

How to access MS-Access metadata tables (eg MSysObjects)
Dear all, I have an MS-Access MDB-file with hundreds of tables and want to know when they were created and/or modified. The MS-Access metadata table MSySObjects includes this information, but I have not managed to read it from SAS. (CLI describe error tells me that I have no reading access) --> Does anybody have a working code example for this? I have tried the libname ACCESS and Libname ODBC variants. Regards Dietrich -- DIETRICH ALTE, Dipl.-Statistiker, Dr. rer. med. Wiss. Projektmanager "Study of Health in Pomerania (SHIP)" Institut f�r Community Medicine - SHIP/KEF EMA...