467,888 Members | 1,528 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,888 developers. It's quick & easy.

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

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:


Expand|Select|Wrap|Line Numbers
  1. Set MyConn = Server.CreateObject("ADODB.Connection")
  2.     MdbFilePath = Server.MapPath("abc")
  3.     MyConn.Open "Driver={xyz 32bit Driver }; 
  4. DBQ=" & MdbFilePath & ";"
  5.  
  6. SQL_query = "SELECT * from table1"
  7.  
  8.     Set RS = MyConn.Execute(SQL_query)
  9.     WHILE NOT RS.EOF
  10.     RS.MoveNext
  11.     WEND
  12.  
  13.     RS.Close
  14.     set RS = nothing  
  15.     MyConn.close
  16.     set MyConn = nothing
Aug 28 '16 #1
  • viewed: 933
Share:
1 Reply
zmbd
Expert Mod 4TB
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...
Sep 6 '16 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.