Executing a Make-Table Query in Access in VBA

Microsoft OFFICE

    Sponsored Links

    Next

  • 1. Problem with Access XP and Source Safe
    Hi! I have installed Access XP and Office developer for XP and I want to add a DB under Source Safe. The SS add-in Work correctly if i use a DB previously added (with Access 2000) to Source Safe, but if I want To add a new DB from XP, that doesn't work. No error is shown... Thanx in advance. Davids
  • 2. custom menu to the Word's 'File' menu
    I need to add a custom menu to the Word's 'File' menu. How can I do this?
  • 3. Error 459 in Excel - no Implements - no WithEvents
    Hello, I have a developed a complex .xla. Since the beginning of the week, when I start one of the forms I receive a 459 error "Objet or Class does not support the set of events". I've read the help, posts on usenet and checked the following points: - WithEvents: only one class with one WithEvents and one instance of this class. I removed the "WIthEvents" for the tests, - Implements: None - Arrays of controls: Not available in VBA (only a VB feature?) - Objects as Sub parameters: One UserForm Sub with one _Object_ parameter (I use it to pass a TextBox Control). I removed it too. All the Modules have the _Option Explicit_. It compiles successfully. The message occurs whenever I start that form and only that form. A breakpoint on the UserForm_Initialize doesn't help to see what is the problem. Actually, I have done no modifications to the code! Thus it should be related to an external cause, like (des)installation of other programs. Does this sound logical ? I would greatly appreciate any clue about that, any lead, ... Thanks Laurent
  • 4. MS Access memofield looses value after read in VB6
    Hello, I am making an xml file with data from MS Access. I have several memo fields in the table. In debug mode, when i hover over the <rsdata("0").value>, I can see the contents of the field. I move the mouse away and back over the same <rsdata ("0").value> and at this point, it indicates "null". Some tests: msgbox with the value is ok, I see the contents of the field. Afterwards it gives me again "null" as a result. I installed the latest VB6 service pack (6B). I use MS Access XP. Thanks for your help. PS: we tried different settings regarding the recordset object and connectionobject, but this couldn't solve the problem. Thanks for your help again. Regards, Johan
  • 5. Problem controling Lexmark printer with API?
    I have a Lexmark 622 and to control the printer I have to use version 1 of their drivers, but, when I do I have kerning problem with letter printing over writing one another. When I use the newer driver the kerning is okay but I can not control the trays or set the printer to dubplex. I have tryed all of their drivers, PCL's and PostScript's. Duplex does work when senting print document from Word directly, but not when the VBA macros are called. Thanks for any help you can give. By the way, it work when I use a HP printer to do the same things. Neal

Executing a Make-Table Query in Access in VBA

Postby Mike Milian » Sat, 15 Nov 2003 06:45:07 GMT

I have a Make-Table query in Access 2002 
named "refreshClients".  All I want to do is execute in 
code similar to the way one executes a regular query. 
eg. "Select * from (Table/Query name)"  I would like to  
capture the SQL statement in a String variable.

eg.

strSQL = "Select * from (Query)" 

I would like to do the same or the Make-Table query 
statement.

Please Help.


Similar Threads:

1.Excel VBA Query Using Access Database Hangs on 2nd Execute

I have a MS Access 2003 Database named "AS400 Fields.mdb".  This
database contains links to tables on an AS400.

In MS Excel 2003, I have VBA code that creates and executes queries
using the Access database, and returns the results to an Excel sheet. 
There are many of these Excel query files and they all work at the
same time on different users' machines.

The first time the query is executed, results are returned to Excel in
usually less than 10 seconds.  However, if the query is executed a
second time (without closing the Excel workbook), the code hangs at
the line surrounded by "=",s in the code below (this was tested with
message boxes and verifying that DB1 and RS1 were closed).  If the Excel 
file containing the VBA code is closed
and re-opened the code executes normally.

Is there a problem in my code?  Am I doing something wrong?

Thanks,
Mike

Private Sub Execute_Query()
    Dim X As Integer
    Dim DB1 As ADODB.Connection
    Dim RS1 As ADODB.Recordset
    Const ConnectionStr As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= Myserver Location Goes here\AS400 Fields.mdb"
    Set DB1 = New ADODB.Connection
    DB1.Open ConnectionStr

    Set RS1 = New ADODB.Recordset
'   NOTE: QryStr is a variable containing the SELECT statement
'=============================
    RS1.Open QryStr, DB1
'=============================        
    'Copy the data
    ActiveCell.Offset(1, 0).Select
    ActiveCell.CopyFromRecordset RS1
    RS1.Close
    DB1.Close
    Set RS1 = Nothing
    Set DB1 = Nothing
End Sub

2.execute a make table query in an access database

The basic idea:
I need to run an existing make table query in a database without the user's 
intervention.  I have tried some code I have found online but none work (thus 
far).

The complete picture:
Same as above, however, I would need to be able to pass along a user input.  
In this case am I better executing an SQL statement from Excel to Acces?  If 
so, how?

Could someone point me in the right direction.  

Thank you,

QB

3.Excel VBA Query Using Access Database Hangs on 2nd Execute

I have a MS Access 2003 Database named "AS400 Fields.mdb".  This
database contains links to tables on an AS400.

In MS Excel 2003, I have VBA code that creates and executes queries
using the Access database, and returns the results to an Excel sheet. 
There are many of these Excel query files and they all work at the
same time on different users' machines.

The first time the query is executed, results are returned to Excel in
usually less than 10 seconds.  However, if the query is executed a
second time (without closing the Excel workbook), the code hangs at
the line surrounded by "=",s in the code below (this was tested with
message boxes).  If the Excel file containing the VBA code is closed
and re-opened the code executes normally.

Is there a problem in my code?  Am I doing something wrong?

Thanks,
Mike

Private Sub Execute_Query()
    Dim X As Integer
    Dim DB1 As ADODB.Connection
    Dim RS1 As ADODB.Recordset
    Const ConnectionStr As String =
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= Myserver Location Goes here\AS400 Fields.mdb"
    Set DB1 = New ADODB.Connection
    DB1.Open ConnectionStr

    Set RS1 = New ADODB.Recordset
'=============================
    RS1.Open QryStr, DB1
'=============================        
    'Copy the data
    ActiveCell.Offset(1, 0).Select
    ActiveCell.CopyFromRecordset RS1
    RS1.Close
    DB1.Close
    Set RS1 = Nothing
    Set DB1 = Nothing
End Sub

4.Query a table from vba in Access

I have a table which contains some data i want to access in my VBA 
module. I basically want to do the equivilent of the following SQL:

SELECT name FROM my_table WHERE id = foo;

How can I do that in the VBA code? Thanks,

David

5.Connection of Excel 07 pivot table to Access Query makes DB read o

Hi,
I created pivot tables in Excel 2007 based on Access queries.  The problem 
is that when I open the Access database, the database is read only.  If I 
close my Excel file, then the database is fine - not read only.  Is there a 
setting change in Excel which would allow me to open the Access DB while the 
Excel file is open and it not be read only?  I checked the Connection 
Properties but could not find the setting.

Thanks,

6. Executing make-table queries with VBA

7. Execute make table query in vba

8. HT execute Access query and HT execute Access function from C#



Return to Microsoft OFFICE

 

Who is online

Users browsing this forum: No registered users and 44 guest