Populate Access table with the RecordSet

[Kaizer]

Hi there,

It's been quite a while I've used Access and have foregotten certain things. Anyway, I get the data from external DBase into the RecordSet and would like to put that into a Access table. In Excel it will be e.g. Cells(2, 1).CopyFromRecordset dataSet. How to do this in Access?

Another question would be how to clear all from the table before populating this table?

Thank you in advance.

[asingh]

1. To get the CSV data you could simply link it.
2. Write a delete query to wash out your Access source table.
3. Create an Update query to append the CSV to the source....?

Put 1--3 in a macro...!

Note
This is a pure non-VBA solution..but should work...
regards,

asingh

[Kaizer]

1. To get the CSV data you could simply link it.
2. Write a delete query to wash out your Access source table.
3. Create an Update query to append the CSV to the source....?

Put 1--3 in a macro...!

Note
This is a pure non-VBA solution..but should work...
regards,

asingh

asing, actually I'm looking for the VBA solution to simplify the process. But thanks anyway.

[asingh]

HI,

You still want a VBA solution [can provide that]..or will the above mentioned do..??

[Norie]

Kaizer

How would a VBA solution 'simplify' things?

All it would be doing would be replacing inbuilt functionality.

Unless there's more to it.

[Kaizer]

Kaizer

How would a VBA solution 'simplify' things?

All it would be doing would be replacing inbuilt functionality.

Unless there's more to it.

Norie, currently I use Excel to get data from external data source every day during the month. By the end of the month it can get to 60.000 lines. I've noticed that Excel hangs when there is too much of data to populate the spreadsheet. So I thought to use Access to pull and store the data. Besides, it's much easier to work with relatevely big number of lines in the Dbase via Access.

That's the reason to move to Access and use the same VBA code as in Excel to pull the data from external data source. But putting it in Access table via VBA is the dark hole for me. Any help to solve it is really appreciated.

[Kaizer]

HI,

You still want a VBA solution [can provide that]..or will the above mentioned do..??
asingh, still want a VBA solution. :) Will appreciate your help on this.

[Norie]

Kaizer

I'm still not sure why you are convinced VBA is the answer.

But then again I've not seen your current code that's pulling the data into Excel.

If you want Access VBA then it's more than likely to be totally different from your current Excel VBA.

Do actually suggest any code I think we really would need more details.

Where is the data coming from?

Where is it going to?

Is it well structured or will it need manipulating?

[Kaizer]

Kaizer

I'm still not sure why you are convinced VBA is the answer.

But then again I've not seen your current code that's pulling the data into Excel.

If you want Access VBA then it's more than likely to be totally different from your current Excel VBA.

Do actually suggest any code I think we really would need more details.

Where is the data coming from?

Where is it going to?

Is it well structured or will it need manipulating?

Norie, actually, to connect to external data via Excel VBA and Access VBA is the same. The end is that data get's into the RecordSet. I know how to put it in Excel Worksheet and don't know how to put it in an Access table. The table should be structured to accept data from the RecordSet. Here is the beginnining of the code I use to connect to external source and store it in the RSet.
Private Sub cmd_Fetch_Click()
Application.ScreenUpdating = False
Dim sapiwConnection As New ADODB.Connection
Dim dataSet As New ADODB.Recordset
Dim sqlString As String
Dim Date_From As String, Date_To As String
Dim UserID As String, Pass As String
Date_From = Range("Date_Begin").Value
Date_To = Range("Date_End").Value
UserID = Range("UserID")
Pass = Range("Password")
sqlString = "SELECT GNKIW.Gxmt0030.REGION, GNKIW.Gxmt0014.GEOG_OFFICE, GNKIW.Gwmt0199.BRNAME, " & _
"GNKIW.Gxmt0014.GEOG_SHIPTO, GNKIW.Gxmt0030.COUNTRY, GNKIW.Gxmt0014.EREVDTE, " & _
"GNKIW.Gxmt0014.DELQTY, GNKIW.Gxmt0014.DELREQ10, GNKIW.Gxmt0014.DOLLAR_PRICE, " & _
"GNKIW.Gxmt0014.COST, GNKIW.Gxmt0014.LOCAL_PRICE, GNKIW.Gxmt0014.MFG_LOCN, " & _
"GNKIW.Gxmt0014.ORDNO10, GNKIW.Gxmt0014.ORDPART, GNKIW.Gxmt0014.REVAMT, " & _
"GNKIW.Gxmt0014.TOPSELL_FLAG, GNKIW.Gxmt0014.CLASS, GNKIW.Gxmt0014.SBO, GNKIW.Gxmt0014.CURCDE, " & _
"GNKIW.Gwmt0199.BRNAME, GNKIW.Gwmt0199.DESCR, GNKIW.Gxmt0014.KPNA, GNKIW.Gxmt0014.CHANTYPE, GNKIW.Gwmt0199.SUBNAME " & _
"FROM GNKIW.Gxmt0014, GNKIW.Gxmt0030, GNKIW.Gwmt0199 " & _
"WHERE (GNKIW.Gwmt0199.Revpart=GNKIW.Gxmt0014.Revpart AND " & _
"GNKIW.Gxmt0030.Geog=GNKIW.Gxmt0014.Geog_Office) AND " & _
"(GNKIW.Gxmt0014.Class IN ('O', 'S') AND " & _
"GNKIW.Gxmt0014.Erevdte BETWEEN '" & Date_From & "' AND '" & Date_To & "' " & _
"AND GNKIW.Gxmt0014.Shipind='B' AND GNKIW.Gxmt0014.Fucb<>'0')"

sapiwConnection.CursorLocation = adUseClient
sapiwConnection.ConnectionString = "Provider=IBMDADB2;

DSN=EUBBDB2A; User ID=" & UserID & "; Password=" & Pass

'MsgBox sapiwConnection.ConnectionString
'open the connection
Application.StatusBar = "Connecting to the database"
sapiwConnection.Open
'MsgBox "Connected"
'MsgBox sqlString
Application.StatusBar = Application.StatusBar & ", fetching data from server"
dataSet.Open sqlString, sapiwConnection, adOpenKeyset, adLockReadOnly
'Assign height and width to variables to these can be used to set the size of the range
DataHeight = dataSet.RecordCount
DataWidth = dataSet.Fields.Count
Me.txtColumns = DataWidth
Me.txtRows = DataHeight

I've checked this code in Access VBA and it worked just fine. Next step is to put the data into Access table that I struggle with.

[Norie]

Kaizer

Sure you can use the Recordset approach in Access.

But why would you when there are other methods in Access to import data.

For example TransferText, TransferSpreadsheet, TransferDatabase.

You might even be able to use an update/append query without code.

[Kaizer]

Kaizer
Sure you can use the Recordset approach in Access.
But why would you when there are other methods in Access to import data.
For example TransferText, TransferSpreadsheet, TransferDatabase.
You might even be able to use an update/append query without code.
Well, I don't know these methods. How the code will look like using the methods you just described?

[asingh]

Kaizer

Sure you can use the Recordset approach in Access.

But why would you when there are other methods in Access to import data.

For example TransferText, TransferSpreadsheet, TransferDatabase.

You might even be able to use an update/append query without code.

As Norie has suggested...above mentioned are good methods. They all get data into the system, keeping in mind structures and constraints.

Yes, the above methods can also be "VBA'ed"....!

1. What is your source data format.....xls, csv, txt.
2. How repititive will be the task.
3. Is the source data dynamic...in the sense will the row counts change.
4. Will columns change too [get tricky]...

regards,
asingh

[Kaizer]

As Norie has suggested...above mentioned are good methods. They all get data into the system, keeping in mind structures and constraints.

Yes, the above methods can also be "VBA'ed"....!

1. What is your source data format.....xls, csv, txt.
2. How repititive will be the task.
3. Is the source data dynamic...in the sense will the row counts change.
4. Will columns change too [get tricky]...

regards,
asingh
asingh,
1. The source is the SAP tables
2. The task will repeat once or twice a week
3. The row counts will change (increase)
4. Columns will not change

[Kaizer]

I couldn't find the methods described by Norie. Does this work when Access is connected to external data source via ODBC?

Anyway, I had to find the solution with the recordset. So here is my code where I loop through the recordset and try to update my access table with the rs data. In the moment when it has to add data to access it gives me an error message: Run-time Error '3251': Operation is not supported for this type of object.
Can you help resolve it, please?
Set db = CurrentDb
Set rs = dataSet
Set rt = db.OpenRecordset("Test")
If Not rs.EOF Then rs.MoveLast: rs.MoveFirst
Do While Not rs.EOF
rt.AddNew
'MsgBox rs.Fields("Region")
rt.Fields ("Region" = Trim(rs.Fields("REGION"))) 'HERE IT STOPS AND I GET ERROR
rt.Fields ("Country" = rs.Fields("COUNTRY"))
rt.Fields ("CurCode" = rs.Fields("CURCDE"))
rt.Update
rs.MoveNext
Loop