VBScript import data table Employee using odbc connection and save as msaccess data

[salmansohail]

I got few odbc connections, where I can import tables from in Access,

However, I'm trying to create a vb Script, which uses the exisitng odbc DSN connection and import or read a table and save that table in my network path as MS ACCESS .mdb format:

Set MyConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("abc")
    MyConn.Open "Driver={xyz 32bit Driver }; 
DBQ=" & MdbFilePath & ";"
 
SQL_query = "SELECT * from table1"
 
    Set RS = MyConn.Execute(SQL_query)
    WHILE NOT RS.EOF
    RS.MoveNext
    WEND
 
    RS.Close
    set RS = nothing  
    MyConn.close
    set MyConn = nothing

[zmbd]

1) Is this really VBScript or VBA?

2) the MDB format is basically depreciated, are you sure you want to store information in a format that may be discontinued sometime in the near future, MS has already yanked support for legacy thru ACC97-MDB files!

3) One way to do this in VBA would be to add a new database to the workspace then use a make-table action query against this new database against the open recordset or perhaps the DoCmd.TransferDatabase depending on the nature of your application. A potentially faster method after creating a second database and setting up the table(s) might be the SELECT.INTO Statement (Microsoft Access SQL) (syntax)) (personally I never build the SQL string in the function as shown in the example, makes it impossible to properly troubleshoot) and the execute method; however, I'm not sure if this will work as you've not included any details about your data source.

Lots of options here...