Converting Access SQL to EXCEL SQL

[jo15765]

I found this posts here:

http://www.dbforums.com/microsoft-access/1611651-define-data-type-make-table-query.html

And I was curious if there was a way to do the same type thing in Excel. Use SQL to make a table in an access database, but I want to take it a step further and then append data into the newly created table.

[Kenneth Hobs]

Doing both of those things are two separate thread issues. Perhaps this will get you started.

To create a table or append data, you can use SQL. To create the table, see: http://vbaexpress.com/forum/showthread.php?t=24883

For an insert SQL example:

Sub demo()
Dim objRS As Object, nwindPath As String
Set objRS = CreateObject("ADODB.Recordset")
nwindPath = ThisWorkbook.Path & "\nwind.mdb"
Dim r As Range
[a1] = "LastName"
[b1] = "FirstName"
[a2] = "Hobson"
[b2] = "Kenneth"
Set r = [a1:b2]
r.Name = "MyRange"
objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath
Set objRS = Nothing
End Sub

[jo15765]

It looks like my original post wasn't descriptive enough. I have a table in an access database, I want to take that table and make a new table in access but only with 4 - 5 of those fields. The database is open to anyone so I was going to try to avoid using a query to do this, and use EXCEL SQL to do it (if that is even possible).

[Bob Phillips]

Rather than create another table with just a subset of the data, with the inherent problems of maintaining the data twice, why not just query the fields that you are interested in from Excel?

[jo15765]

I was wanting to create a secondary table with a subset of the data because I need to use this table as well as 2 other tables, to build a query which will hold the final results that I want to pull into Excel. I was thinking run a make table because I am unable to set up my query the way I need to since one of my join fields is a memo in one table, and a text in another table, so the join errors.

The 1st workaround that came to mind (to me at least) was to just create a make table, and set the field as text so that way, the join condition is met.

[Bob Phillips]

I still don't see the point of another table. Your query from Excel can join three tables, you can put a WHERE clause to get only the subset data. Another table is asking for problems.

[BrianMH]

Can you update the tables? You could change the memo to text. This would resolve your need for a third table.

[jo15765]

It's a linked SQL table, and other programs are reliant upon it so I can't just change the table via SQL. That's why I was wanting to run a make-table.

[Aflatoon]

you can run a MT query just as you can any other using SQL like


SELECT tblSource.Field1 INTO tblNew FROM tblSource;

which you can just execute from a connection object