What do I change to run macro in access to table

[grichey]

Hello, I've been using the below with great success in excel. The problem is I've encountered a situation in which row count greatly exceeds 65000. How do I run the below to an access table?

This is my first attempt at using vba in access so apologies if this seems like a dumb questions.

sub getdata()

Dim objWeb As QueryTable
'Get info from web
Set objWeb = ActiveSheet.QueryTables.Add( _
Connection:="URL;http://www.google.com", _
Destination:=Range("A1"))

With objWeb

.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.Refresh BackgroundQuery:=False
.SaveData = True
End With

[OTWarrior]

what is this supposed to do? and why does it error on row 65000?

[grichey]

I just used google.com as an example. I can post the actual link I'm using if it's of any use. It's not erroring. There is simply more than 65k rows of data. It does what it's supposed to in excel, excel just doesn't have enough rows to fit it all.


This is posted on ee as well now. Hopefully someone will have a clever method! If a solution is arrived at on ee, I'll put it up here as well.

[OTWarrior]

Don't worry about the data you are putting into it. what does this actually do?

does it record the source code for the site?
does it record all the text?
does it use numbers on the site to make the screen flash pretty colours?

What should this code do? and why should 65000 rows not be enough?

Couldn't you do a test to see if it has reached the 65000 limit? and if it does to continue onto the next worksheet?

[grichey]

Don't worry about the data you are putting into it. what does this actually do?
The url I am pulling is a txt file more or less of records.



does it record the source code for the site?
does it record all the text?
?
The source code. The file(s) are .html and .txt so the source is more or less the same as the data. Yes it brings in all the text which I will then clean up and remove headers etc so it's one long list of records.

does it use numbers on the site to make the screen flash pretty colours?? ? It has nothing to do with flashing anything.


What should this code do? and why should 65000 rows not be enough?
Couldn't you do a test to see if it has reached the 65000 limit? and if it does to continue onto the next worksheet?
65k rows is not enough because there is more than 65k rows of data. From my understanding web query pulls the entire set as 1 so you cannot test to see if it's reached the limit. Additionally, due to the volume of data, access is just better suited for the relational nature of the data once it's brought in.

[OTWarrior]

I suggested flashy colours as you did not say what your code does. It was a joke is all.

That sounds like an awful lot of data you are pulling in. Will this be a regular occurance for you? Or is this a one time thing?

Does it work fine in Excel? If so, you could probably get Access to load excel, call the macro, then pull in the data in stages (since excel will have it loaded).

Are you going to be saving each import into just one table in Access, or could you use multiple tables? (IE: one for each import such as tbl_google, tbl_youtube)

[OBP]

Gavin, it would appear that Access has the same "Connection" function as Excel. If you look at the Access VBA Editor's help for Connection and Connectionstring you get this for the string, which appears to include URLs as sources.
"Indicates the information used to establish a connection to a data source.
Settings and Return Values
Sets or returns a String value.
Remarks
Use the ConnectionString property to specify a data source by passing a detailed connection string containing a series of argument = value statements separated by semicolons.
ADO supports five arguments for the ConnectionString property; any other arguments pass directly to the provider (http://www.vbaexpress.com/forum/mddefdataprovider.htm) without any processing by ADO. The arguments ADO supports are as follows.
Argument Description Provider= Specifies the name of a provider to use for the connection. File Name= Specifies the name of a provider-specific file (for example, a persisted (http://www.vbaexpress.com/forum/mddefpersist.htm) data source object) containing preset connection information. Remote Provider= Specifies the name of a provider to use when opening a client-side (http://www.vbaexpress.com/forum/mddefclienttier.htm) connection. (Remote Data Service only.) Remote Server= Specifies the path name of the server to use when opening a client-side connection. (Remote Data Service only.) URL= Specifies the connection string as an absolute URL (http://www.vbaexpress.com/forum/mddefabsoluteurl.htm) identifying a resource, such as a file or directory.
After you set the ConnectionString property and open the Connection (http://www.vbaexpress.com/forum/mdobjconnection.htm) object, the provider may alter the contents of the property, for example, by mapping the ADO-defined argument names to their provider equivalents.
The ConnectionString property automatically inherits the value used for the ConnectionString argument of the Open (http://www.vbaexpress.com/forum/mdmthcnnopen.htm) method, so you can override the current ConnectionString property during the Open method call.
Because the File Name argument causes ADO to load the associated provider, you cannot pass both the Provider and File Name arguments.
The ConnectionString property is read/write when the connection is closed and read-only when it is open.
Duplicates of an argument in the ConnectionString property are ignored. The last instance of any argument is used.
Remote Data Service Usage When used on a client-side Connection object, the ConnectionString property can include only the Remote Provider and Remote Server parameters."

[grichey]

That sounds like an awful lot of data you are pulling in. Will this be a regular occurance for you? Or is this a one time thing?

Does it work fine in Excel? If so, you could probably get Access to load excel, call the macro, then pull in the data in stages (since excel will have it loaded).

Are you going to be saving each import into just one table in Access, or could you use multiple tables? (IE: one for each import such as tbl_google, tbl_youtube)

This would be a periodic thing. Maybe once every 1 - 3 months. It works in excel up to pulling the data in which would then need to be broken up into the correct fields as opposed to long strings.

Getting access to load excel doesn't get around the size limitation as I'd still have to figure out how to read it in piece meal instead of in 1 shot which I'm not sure how to do.

There are 3 tables I need to import which are made up of about 30 online files/urls each which I will then link up by the key code present in all 3 to get the final complete list.

[OTWarrior]

Once Excel has load the files, use access to call the references to a collection of cells.

something like (pueso code):

a = Excel.Cells("A1", "A2")

[grichey]

Once Excel has load the files, use access to call the references to a collection of cells.

something like (pueso code):

a = Excel.Cells("A1", "A2")

How does this get around the 65k limit?

[grichey]

OBP: I am trying to figure out how to make use of your suggestion.

[OTWarrior]

How does this get around the 65k limit?

you can import different rows from excel to different tables in access. Not ideal, but if Access has a maximum limit, may be the only way around.

But keep trying with OBP's suggestion, as it looks less "ad-hoc" than my idea :D

I still think 65k records is an awful lot for one table. There isn't any way you can divide your data up?

[CreganTur]

you can import different rows from excel to different tables in access. Not ideal, but if Access has a maximum limit, may be the only way around.

Access Table Limits:

Number of fields in a table:255
Table size:




2 gigabyte minus the space needed for the system objects - Acc 2003
2 gigabytes - Acc XP
1 gigabytes - Acc 2000You'll also need to give some careful consideration to the data type you use, since this will determine a lot about how much data can be entered into each field of an individual record:

Data Type | Limit
Text | alphanumeric- 256 characters
Memo | alphanumeric - 65,535 characters


And if you're going to store any of this data as one of the Number data type, then you'll have to take that into consideration as well.

[grichey]

you can import different rows from excel to different tables in access. Not ideal, but if Access has a maximum limit, may be the only way around.

But keep trying with OBP's suggestion, as it looks less "ad-hoc" than my idea :D

I still think 65k records is an awful lot for one table. There isn't any way you can divide your data up?


Please let me know where I'm losing you here. You're suggesting importing into excel the way which I have working then bringing it from there to access.

Unless I'm missing something, this still doesn't address the root problem of my current method using excel not importing all of the data due there being more than 65k rows of data. One single file I'm importing is roughly 70k rows. Another 25 files will be brought in and appended to this list. I don't think the 65k is too many at all. That's what access is great for :thumb . I just need to figure out how to get it in there.

[grichey]

The size of the db should be significantly less than 2 gigs. There wont be any reporting or anything saved in there. There will only be about 10 fields Name Address1 Address2 City State Phone Serial# and maybe 1 for memo. But that's about it. The only limitation with access I'm trying to overcome is finding an alternative to webquery object.


Access Table Limits:

Number of fields in a table:255
Table size:




2 gigabyte minus the space needed for the system objects - Acc 2003
2 gigabytes - Acc XP
1 gigabytes - Acc 2000You'll also need to give some careful consideration to the data type you use, since this will determine a lot about how much data can be entered into each field of an individual record:

Data Type | Limit
Text | alphanumeric- 256 characters
Memo | alphanumeric - 65,535 characters


And if you're going to store any of this data as one of the Number data type, then you'll have to take that into consideration as well.

[OTWarrior]

Please let me know where I'm losing you here. You're suggesting importing into excel the way which I have working then bringing it from there to access.

Unless I'm missing something, this still doesn't address the root problem of my current method using excel not importing all of the data due there being more than 65k rows of data. One single file I'm importing is roughly 70k rows. Another 25 files will be brought in and appended to this list. I don't think the 65k is too many at all. That's what access is great for :thumb . I just need to figure out how to get it in there.

What I am suggesting, is with that single 70k rows file, couldn't you split it into two tables of 35k rows?

[grichey]

Not at least in the way querytables works I don't think. I'm nearly positive it comes in as one big bang.