Save Recordset in VB to Access Table

[zenenigma]

I have a SQL table which I am querying from. The VB program allows a user to specify "customer number" and it will pull up all records with a related customer number in the SQL table and populate a truedbgrid.

I want to let the user have the option to click on a button and "save" these resulting records to an Access Table (name which they will input). I am using ADO for the SQL query to pull the data in the first place, but I'm having trouble figuring out how to send the resulting recordset to Acess.

Any help would be greatly appreciated.

-ZE

 

[dtqbterror]

To do this you will need to create a ADO Connection to the access database then use then use a SQL Statement to create your table.
(CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

Once the table is created in access you can loop thru your open record set and insert you data into it.
(INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])

 

[BobRodes]

While the above is quite correct, this might be a little simpler, assuming I have understood what you are asking to do.

I believe that all you really need to do is a SELECT INTO with the same criteria as the select statement that you used to open the ADO Recordset. For example, when you opened the recordset, if you used the sql statment

"select * from customers where name = 'Smith'"

you could now do

"select * into " & UserSuppliedString & " from customers where name = 'Smith'"

That would save the tedium of creating the table and the multiple insert statements and all.

Bob Rodes

 

[dtqbterror]

I dont think that will work when you are getting the original data from a SQL Server DB and then trying to transfer the data into Access. You will have to have two different connection objects on for SQL Server and the other for Access.

 

[BobRodes]

Sorry, I missed that entirely. Quite correct, that won't work. However, the other thing you can do is go into SQL Server and set up Access as a Linked Server. That should allow you to work with both tables. Or, you can go into Access and set up your SQL server Table as a Linked Table via ODBC.

Anyway, a lot of paths to nirvana...

Bob

 

[zenenigma]

>To do this you will need to create a ADO Connection to the access database then use then use a SQL Statement to create your table.
(CREATE [TEMPORARY] TABLE table (field1 type [(size)] [NOT NULL] [WITH COMPRESSION | WITH COMP] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

Once the table is created in access you can loop thru your open record set and insert you data into it.
(INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])
----------------------------
I'm having trouble doing this.

I am able to make an ADO connection to the database, I assume leaving the recordsource blank, because I'm making a table. I'm having a problem having the SQL statement run for that database connection (create table in access). My current connection name is adodc2.

Also, if someone could help me with the details of the Insert Into statement, it would be much appreciated.

-ZE

 

[mouser000]

well u do one thing

1. open a different ADO Connection for Access Database.
2. when connection done successfully, use ADO CONNECTION
objects Execute method, ex- Conn.Execute. to create table in access database.
SQL - Create table <tablename> (name text (20),...)

conform for SQL supported by Access, by running it in
Access environment as a Access query.

3. declare a recordset to newly created table in access (rs1)

4. follow the previous loop solution i,e.,
rs.movefirst
do while not rs.eof
rs1.addnew
rs1(&quot;field&quot = rs(&quot;field&quot
rs1.update
rs.movenext
loop

 

[zenenigma]

I'm using Microsoft ADO Data Control 6 (SP4) and getting an error when trying to use the &quot;execute&quot; method. It is also not listed on the adodc1. dropdown list. Any help would be much appreciated.

 

[zenenigma]

What I'm trying to do: Save the SQL recordset (rs) to a table within an access database. &quot;rs&quot; is my SQL recordset, sql is my SQL String

I get an error on &quot;as database&quot; - &quot;Method or Data Member not Found&quot;. I'm using ADO 6.0 and VB6. Access Version is 97.

So far:

--------------------

dim sql as string
Dim dbdatabase As Database
dim rs as recordset
dim rs1 as recordset

FiletoSave = InputBox(&quot;Please choose a table name&quot


sql = &quot;CREATE TABLE &quot; & FiletoSave & &quot; ( Terminal varchar (15) NOT NULL , &quot; & _
&quot;Trans_Date datetime NOT NULL , Card_Num varchar (25) NOT NULL , &quot; & _
&quot;Amount money NOT NULL , &quot; & _
&quot;Address varchar (40) NULL , City varchar (25) NULL , &quot; & _
&quot;State varchar (2) NULL , Merchant varchar (25) NULL)&quot;

Set dbdatabase = OpenDatabase(&quot;L:\2kRepository\TablesToAdd.mdb&quot

dbdatabase.Execute sql

rs = Adodc1.Recordset
rs1 = Adodc2.Recordset


rs.MoveFirst
Do While Not rs.EOF
rs1.AddNew
rs1(&quot;Field&quot = rs(&quot;field&quot
rs1.Update
rs.MoveNext
Loop

-----------------

Any help would be appreciated, this has been killing me for weeks.

 

[kk143]

just try this program for conecting to access:

with con
.connectionstring=&quot;datasource= &quot; (ur datasource identity)
.provider=&quot;Microsoft.jet.oledb.4.0&quot;
.open
endwith

** now u can have the access that u r trying to have****

 

[zenenigma]

with con
.connectionstring=&quot;datasource= &quot; (ur datasource identity)
.provider=&quot;Microsoft.jet.oledb.4.0&quot;
.open
end with

-----------------

My program gave me a &quot;Could not find installable ISAM&quot; when it got to the .open function.

Also, with your code I assume I'd need to:

dim con as adodb.connection
set con = new adodb.connection