Sqlite import & export with macro (1 Viewer)

Ocicek

New member
Local time
Today, 20:23
Joined
Jan 19, 2021
Messages
25
Hello friends. I have an application running on Android. The application works with sqlite database. I load the list of values to be filled in to this sqlite, then this sqlite file comes to us.

I need to do the empty and filled version of this sqlite database with microsoft access database from now on.

I installed the sqlite 3 odbc driver. So I can create this file. And I can restore it. There is no problem here, but the problem is that the sqlite odbc database is asking me for the filename in its interface every time. I do not know if it is possible to write this name with an automation. (Attached screenshots)

When I try to make it as a linked table, I have to copy and store each file separately, it seems very complicated.

So how can I get the file name of the sqlite odbc database from a form in my acces database when importing or exporting?

Is there someone to help us about it?
 

Attachments

  • Ekran Resmi 2021-04-22 19.08.49.png
    Ekran Resmi 2021-04-22 19.08.49.png
    266.1 KB · Views: 76
  • Ekran Resmi 2021-04-22 19.09.19.png
    Ekran Resmi 2021-04-22 19.09.19.png
    156.5 KB · Views: 73

bastanu

AWF VIP
Local time
Today, 10:23
Joined
Apr 13, 2010
Messages
826
Link it manually once then turn on view system objects under File\Options\Current Database\Navigation and look for the connection string in msysObjects table (field name is Connect). It should have the driver info, database, user and password (make you you checked the Save password check box when you link it). Use that in VBA to dynamically replace the connection string based on the value in your form.
Code:
Dim tdf as DAO.TableDef,sConnect as String

Set tdf=CurrentDb.TableDefs("tblYourLinkedTable")
sConnect="SQL Lite Driver;Database:" & Me.SQLLIteFile & ", User:....." 'use the connection info from msysObjects to build this
tdf.Connect=sConnect
tdf.RefreshLink
Set tdf=Nothing

Cheers,
 
Last edited:

Ocicek

New member
Local time
Today, 20:23
Joined
Jan 19, 2021
Messages
25
Thanks Bastanu. I'll try that immediately.
 

Ocicek

New member
Local time
Today, 20:23
Joined
Jan 19, 2021
Messages
25
Bastanu

I'd try to do it but I think I have to learn more about declarations and commands on VB.

My connection string is like "DSN=SQLite3 Datasource;Database=C:\Dafsmart\Europe3.db;StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;PWD=" on my mssysobjects table

We need to change "C:\Dafsmart\Europe3.db" part of the string.

What I should write for customize the "sConnect="SQL Lite Driver;Database:" & Me.SQLLIteFile & User:....."" part of code you have sent ?
 

bastanu

AWF VIP
Local time
Today, 10:23
Joined
Apr 13, 2010
Messages
826
When linking can you try to create a file DSN instead of machine DSN, they are easier to work with and you can read it with a Word editor like NotePad.

Try this (assumes you run it from a form that has a textbox named SQLLiteFile that holds the full path the the database file to use):
sConnect="DSN=SQLite3 Datasource;Database=" & Me.SQLLiteFile & ";StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;PWD="

Cheers,
 

Ocicek

New member
Local time
Today, 20:23
Joined
Jan 19, 2021
Messages
25
@bastanu thanks for advice, I am not a coder and I'd like to know that we can use connection script with a txt file.

and yes I had a sqlite filename textbox.

So I prepared my code like that:

Dim tdf As DAO.TableDef, sConnect As String

Set tdf = CurrentDb.TableDefs("okumalar")
sConnect = "DSN=SQLite3 Datasource;Database=" & Me.SQLLiteFile & ";StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;PWD="
tdf.Connect = sConnect
tdf.RefreshLink
Set tdf = Nothing

End Sub


But I am getting 3420 runtime error.

What am I doing wrong ?
 

bastanu

AWF VIP
Local time
Today, 10:23
Joined
Apr 13, 2010
Messages
826
Maybe try
Code:
Dim tdf As DAO.TableDef,db as DAO.Database sConnect As String

Set db=CurrentDb
Set tdf = db.TableDefs("okumalar")
sConnect = "DSN=SQLite3 Datasource;Database=" & Me.SQLLiteFile & ";StepAPI=0;SyncPragma=NORMAL;NoTXN=0;Timeout=100000;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0;JournalMode=;OEMCP=0;LoadExt=;BigInt=0;JDConv=0;PWD="
tdf.Connect = sConnect
tdf.RefreshLink
Set tdf = Nothing

Just to confirm - on the form where you run this from need a textbox called SQLLiteFile.

On what line do you get the error?

Cheers,
 

Users who are viewing this thread

Top Bottom