Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 34
  1. #16
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I've only ever "played" with SSMA and it seemed to just work for me.



    Did you create a new empty database on Azure as a "target"?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  2. #17
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

  3. #18
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Minty View Post
    I've only ever "played" with SSMA and it seemed to just work for me.

    Did you create a new empty database on Azure as a "target"?
    Yes I created a new database called FRT_Backend.

    I think my issue is that this FRT_Backend database is somehow not being pulled through, like when I do the ODBC file I only get "master" when it should have the option to select "FRT_Backend" as per below screenshot.

    Click image for larger version. 

Name:	DSN.PNG 
Views:	35 
Size:	48.9 KB 
ID:	44772


    Those are all on migration of data etc, I have done that and checked it, my data and tables are in my Azure SQL database, its the linking of tables in Access Frontend thats my issue now.

  4. #19
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sorry, I misunderstood. I thought (Post #15) the Access tables didn't migrate properly/at all.

  5. #20
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Hi All

    Ok I have solved one problem and found another.

    The reason why ODBC wasnt showing FRT_Backend database was because I was using the old driver, installed the new and set it all up and now directing to FRT_Backend.

    Then in Access, I created external data sources using the ODBC and they came in ok, so I have the Azure SQL Database tables in Access.

    So then I renamed the new SQL database tables in Access to the correct names (removing the dbo part) so that all the forms/queries/code would work as before.

    But now when I turn on the database and login to SQL, access thinks for a moments and then disappears completely??

  6. #21
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    What's the code being used - put some break points in or some message boxes so you can see where it gets to.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #22
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    There is a bunch of code, lines and lines of code.

    But there is no error checking yet so if it was code related it should come up with the standard break message, but its just opening and then closing immediately like a crash to desktop.



    Moving on from the above (which I'll keep working on), I need to get this into a "working" format asap, I will keep working on it however so please keep helping the issues I've listed, but for right now I want to implement a bandaid solution as below:

    I have the SQL tables linked in Access (called dbo_FRT_Table for example), and I have the original tables in the Access file, I want to use a form load (or application load) VBA code to:

    1. Delete all data in the local tables and
    2. Copy "ALL" data (including PK ID) from the SQL tables to the existing tables (from dbo_FRT_Table to FRT_Table for example).

    Obviously this is a bandaid solution, but all existing code works with the original local tables so thats how I want to proceed right now to get this into a working state.

    I have the first part worked out to delete the data in local tables, just can't seem to find a catch all VBA code to simply copy ALL data from SQL table to local table, my current code is as below:


    Code:
    Private Sub Form_Load()
    'Will turn on the below + maybe add more to get the final look I want
    
    'DoCmd.ShowToolbar "Ribbon", acToolbarNo
    
    
    
    
    'Deletes all local records from tables
    DoCmd.SetWarnings False
    
    
    DoCmd.RunSQL "DELETE * FROM FRT_Table"
    DoCmd.RunSQL "DELETE * FROM FRT_Additionals_Table"
    DoCmd.RunSQL "DELETE * FROM Locals_Table"
    DoCmd.RunSQL "DELETE * FROM Transits_Table"
    
    
    DoCmd.SetWarnings True
    
    
    'Uploads data from SQL to local tables
    'Need something here
    
    
    
    
    
    End Sub
    Any simple ideas?

  8. #23
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Hi All

    Ok I have the below code now which seems to work:

    Code:
    Private Sub Form_Load()
    'Will turn on the below + maybe add more to get the final look I want
    
    'DoCmd.ShowToolbar "Ribbon", acToolbarNo
    
    
    
    
    ' Deletes all local records from tables
    DoCmd.SetWarnings False
    
    
    DoCmd.RunSQL "DELETE * FROM FRT_Table"
    DoCmd.RunSQL "DELETE * FROM FRT_Additionals_Table"
    DoCmd.RunSQL "DELETE * FROM Locals_Table"
    DoCmd.RunSQL "DELETE * FROM Transits_Table"
    
    
    'Uploads data from SQL to local tables
    DoCmd.RunSQL "INSERT INTO FRT_Table SELECT dbo_FRT_Table.* FROM dbo_FRT_Table;"
    DoCmd.RunSQL "INSERT INTO FRT_Additionals_Table SELECT dbo_FRT_Additionals_Table.* FROM dbo_FRT_Additionals_Table;"
    DoCmd.RunSQL "INSERT INTO Locals_Table SELECT dbo_Locals_Table.* FROM dbo_Locals_Table;"
    DoCmd.RunSQL "INSERT INTO Transits_Table SELECT dbo_Transits_Table.* FROM dbo_Transits_Table;"
    
    
    DoCmd.SetWarnings True
    
    
    End Sub
    However when I try the reverse below:

    Code:
    Sub UpdateSQLServer()
    ' Deletes all SQL records from tables
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "DELETE * FROM dbo_FRT_Table"
    DoCmd.RunSQL "DELETE * FROM dbo_FRT_Additionals_Table"
    DoCmd.RunSQL "DELETE * FROM dbo_Locals_Table"
    DoCmd.RunSQL "DELETE * FROM dbo_Transits_Table"
    
    
    'Uploads data to SQL from local tables
    DoCmd.RunSQL "INSERT INTO dbo_FRT_Table SELECT FRT_Table.* FROM FRT_Table;"
    DoCmd.RunSQL "INSERT INTO dbo_FRT_Additionals_Table SELECT FRT_Additionals_Table.* FROM FRT_Additionals_Table;"
    DoCmd.RunSQL "INSERT INTO dbo_Locals_Table SELECT Locals_Table.* FROM Locals_Table;"
    DoCmd.RunSQL "INSERT INTO dbo_Transits_Table SELECT Transits_Table.* FROM Transits_Table;"
    
    DoCmd.SetWarnings True
    
    
    End Sub
    All the delete commands work, but then on the insert commands only "DoCmd.RunSQL "INSERT INTO dbo_FRT_Table SELECT FRT_Table.* FROM FRT_Table;" works, the other ones (which as far as I can tell are identical) do not insert into the relevant dbo tables?

    Any ideas?
    Last edited by stildawn; 03-25-2021 at 04:32 PM.

  9. #24
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That's a messy solution. I would fix the original issue rather than wasting time with the Band-Aid.

    Your original error message intrigues me;
    But now when I turn on the database and login to SQL
    What does login to SQL actually mean? If the tables are linked there should be no need to login to SQL.
    Can you post up just the initial code on whatever form or menu you are using when the DB is first open.

    Debugging this should be quite easy - at least in terms of finding the culprit.
    Open the DB while holding down the shift key. This will prevent any code running.

    If you have a million lines of code simply put a simple
    Code:
    Debug.Print "I got here : 1"
    Exit Sub
    Really near the beginning
    If it reaches it move it after the next chunk of code - remember to save the code each time then reopen the DB, or try opening your initial form.
    Make sure you comment out any error handling - you need to see the errors.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  10. #25
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by Minty View Post
    That's a messy solution. I would fix the original issue rather than wasting time with the Band-Aid.

    Your original error message intrigues me;


    What does login to SQL actually mean? If the tables are linked there should be no need to login to SQL.
    Fixed that, just had to click "save password" when I set up the new linked tables.

    Quote Originally Posted by Minty View Post
    Can you post up just the initial code on whatever form or menu you are using when the DB is first open.

    Debugging this should be quite easy - at least in terms of finding the culprit.
    Open the DB while holding down the shift key. This will prevent any code running.

    If you have a million lines of code simply put a simple
    Code:
    Debug.Print "I got here : 1"
    Exit Sub
    Really near the beginning
    If it reaches it move it after the next chunk of code - remember to save the code each time then reopen the DB, or try opening your initial form.
    Make sure you comment out any error handling - you need to see the errors.
    Ok its not VBA related, the form load code which is the code that runs when you start the DB can be blank and it still crashes. I'm fairly sure its to do with my queries (which get called to build the main form), even if I start it holding shift, if I try to run any of my queries to crashes instantly, well sometimes access restarts and asks me to save a backup but I think thats just part of the crashing thing.

    Renaming tables woudnt cause queries to break? When I added in the SQL tables, they were called dbo_FRT_Table for example, I then renamed the local FRT_Table to old_FRT_Table, and renamed dbo_FRT_Table to FRT_Table effectively replacing the FRT_Table with the SQL one??

    Here is the DB if you want to try
    Freight Rate Program Master v0.60.zip




    In regards to the bandaid, its a decent solution for now as it means I can deploy it without working through all the optimising that obviously needs to be done to get it up to web standards, I need to deploy this within a few days ideally.

    Once deployed especially if using the Azure SQL database, I'm free to take my time to optimise and work on getting it to the level it needs to be while the users can still get functionality out of it, the amount of data in it in the early few months wont be that much so it wouldnt be that slow just loading in whole data tables on each opening.

  11. #26
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    In your database in the options under Current Database, did you have or do you have, "Name AutoCorrect" turned on.
    If you do that will have probably broken things.

    If you have a back up, go to that , turn that option off (for ever!! it leads to DB file corruption) and then change your names etc.

    The problem with the band-aid local table solution is that any updates will be overwritten by the last user to upload their copies...
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  12. #27
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Ah thanks will check that setting tomorrow.



    I'm aware regarding the each user will overwrite the database, but there will only be 1 or 2 users who actually update anything, all other users will just be using the info.

  13. #28
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Ok that seems to get me a step closer, its not crashing constantly now.

    However it now has "data type mismatch in criteria expression" in most of my queries?

    Updated DB below:
    Freight Rate Program Master v0.61.zip




    Can I please have some help on the SQL Insert code not working? As I to get this working asap and this way will work if I can just get the local tables to update the SQL tables:

    Code:
    Private Sub Form_Load()
    'Will turn on the below + maybe add more to get the final look I want
    
    'DoCmd.ShowToolbar "Ribbon", acToolbarNo
    
    
    
    
    ' Deletes all local records from tables
    DoCmd.SetWarnings False
    
    
    DoCmd.RunSQL "DELETE * FROM FRT_Table"
    DoCmd.RunSQL "DELETE * FROM FRT_Additionals_Table"
    DoCmd.RunSQL "DELETE * FROM Locals_Table"
    DoCmd.RunSQL "DELETE * FROM Transits_Table"
    
    
    'Uploads data from SQL to local tables
    DoCmd.RunSQL "INSERT INTO FRT_Table SELECT dbo_FRT_Table.* FROM dbo_FRT_Table;"
    DoCmd.RunSQL "INSERT INTO FRT_Additionals_Table SELECT dbo_FRT_Additionals_Table.* FROM dbo_FRT_Additionals_Table;"
    DoCmd.RunSQL "INSERT INTO Locals_Table SELECT dbo_Locals_Table.* FROM dbo_Locals_Table;"
    DoCmd.RunSQL "INSERT INTO Transits_Table SELECT dbo_Transits_Table.* FROM dbo_Transits_Table;"
    
    
    DoCmd.SetWarnings True
    
    
    End Sub
    However when I try the reverse below:

    Code:
    Sub UpdateSQLServer()
    ' Deletes all SQL records from tables
    DoCmd.SetWarnings False
    
    DoCmd.RunSQL "DELETE * FROM dbo_FRT_Table"
    DoCmd.RunSQL "DELETE * FROM dbo_FRT_Additionals_Table"
    DoCmd.RunSQL "DELETE * FROM dbo_Locals_Table"
    DoCmd.RunSQL "DELETE * FROM dbo_Transits_Table"
    
    
    'Uploads data to SQL from local tables
    DoCmd.RunSQL "INSERT INTO dbo_FRT_Table SELECT FRT_Table.* FROM FRT_Table;"
    DoCmd.RunSQL "INSERT INTO dbo_FRT_Additionals_Table SELECT FRT_Additionals_Table.* FROM FRT_Additionals_Table;"
    DoCmd.RunSQL "INSERT INTO dbo_Locals_Table SELECT Locals_Table.* FROM Locals_Table;"
    DoCmd.RunSQL "INSERT INTO dbo_Transits_Table SELECT Transits_Table.* FROM Transits_Table;"
    
    DoCmd.SetWarnings True
    
    
    End Sub
    All the delete commands work, but then on the insert commands only "DoCmd.RunSQL "INSERT INTO dbo_FRT_Table SELECT FRT_Table.* FROM FRT_Table;" works, the other ones (which as far as I can tell are identical) do not insert into the relevant dbo tables?

    Any ideas?

  14. #29
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Having had time to look at your example db your problem appears to be the Date field data types that the Migration assistant has choosen in Azure.

    If you look at your tables in Design mode they are showing as Date/Time Extended. Change theses in Azure to either SmallDateTime or DateTime and most of your issues should disappear.

    For proof open the List8 row source and try opening it in datasheet view.
    Union query Master_Data2 won't open either.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  15. #30
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    If using SSMS I chnage all my datetime2 columns to datetime, what issues could I potentially run into?

    Will the data already in there be changed or removed etc?
    Will Access tables work automatically with the new datetime SQL format? Or do I need to do something to Access tables etc as well?

    Cheers

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Hosting
    By Perfac in forum Access
    Replies: 1
    Last Post: 09-27-2018, 01:34 PM
  2. Web hosting
    By Perfac in forum Access
    Replies: 2
    Last Post: 05-04-2018, 04:02 PM
  3. DB hosting solutions
    By ahill48 in forum Access
    Replies: 8
    Last Post: 08-20-2017, 10:26 AM
  4. Web Hosting
    By momodoujimnjie in forum Access
    Replies: 1
    Last Post: 09-01-2015, 09:59 AM
  5. Replies: 3
    Last Post: 01-22-2013, 03:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums